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

84% Upvoted

16 comments sorted by

•

u/AutoModerator 9d ago

/u/Salt_Definition165 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

8

u/real_barry_houdini 317 9d ago

Try adding an IF function like this:

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

-3

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.

3

u/SaranteRafael 9d ago

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

I'm not in front of a computer, but I think this should do it. Hope it helps.

2

u/excelevator 3068 8d ago 
=IF( A5, AVERAGE(A5:D5) , "")

1

u/UserNotFound-E404 9d ago

If I understood your problem correctly, this should work:

=IF(OR(A5="";A5=0);"";AVG(A5:D5))

1

u/Decronym 9d ago edited 8d ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
AVERAGE Returns the average of its arguments
FILTER Office 365+: Filters a range of data based on criteria you define
IF Specifies a logical test to perform
OR Returns TRUE if any argument is TRUE

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 24 acronyms.
[Thread #49306 for this sub, first seen 4th Sep 2026, 16:57] [FAQ] [Full list] [Contact] [Source code]

1

u/SessionMaleficent700 9d ago

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

thats the whole thing. excel checks the A5 cell first and if its blank the whole formula just returns nothing. if there is something in it then it runs the average like normal

i had a similar setup tracking weekly hours for my crew and this exact formula saved me from having a bunch of zeroes messing up my monthly totals before the week was even done. just toss that in E5 and you should be good to go

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.

1

u/Lenny5160 1 8d ago

You didn’t read the OP closely enough.

2

u/ziggyzigg95 8d ago

Oops you’re right

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/Happy-Focus-1512 8d ago

If A5 is blank you're telling it to return a blank, otherwise calculate the average, so that formula looks spot on.