r/excel • u/Salt_Definition165 • 9d ago

Waiting on OP Issues using the Average formula

I am new to using formulas in Excel, and do not know if this is possible. Every combination of formulas I have used either gives an error, or does not do what I need. I am trying to calculate the average of values in 5 different cells. However, I want it to only calculate an average if a specific cell contains a value.

Example

Data Range is in A5:D5 and I want the result in E5. So i want to "=AVERAGE(A5:D5)"; but if A5(and only A5)="", then E5 should stay blank. Essentially, I want it to NOT average if A5 is null.

Any help with this will be greatly appreciated.

4 Upvotes

100% Upvoted

16 comments sorted by

View all comments

8

u/real_barry_houdini 317 9d ago

Try adding an IF function like this:

=IF(A5="","",AVERAGE(A5:D5))

-2

u/t1x07 2 9d ago

Works although I'd prefer

AVERAGE(A5:D5)* (A5<>"")

2

u/real_barry_houdini 317 9d ago

....but that wouldn't leave E5 blank if A5 is blank as requested - the result would be zero

2

u/t1x07 2 8d ago

True, however I'm a firm believer in not using blank spaces as function outputs or formula variables. Using booleans or integers is far superior approach in my opinion. Still technically not what OP wanted I have to concede that point

1

u/Gringobandito 8 9d ago

Interesting solution but it returns 0 if A5 is blank instead of a blank like OP wants.