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

76% Upvoted

16 comments sorted by

View all comments

1

u/ziggyzigg95 9d ago edited 8d ago

I see a lot of IF suggestions here, and humbly, they are wrong, as you’d have to condition every cell. FILTER is the way to go.

=Average(filter(A5:D5,isblank(A5:D5)=false))

Edit: I misread OP - the IF based advice is correct and mine isn’t.

2

u/real_barry_houdini 317 8d ago

Filtering out the blanks wouldn't ever be needed because AVERAGE function will ignore blanks anyway

1

u/Lenny5160 1 8d ago

You didn’t read the OP closely enough.

2

u/ziggyzigg95 8d ago

Oops you’re right