Need assistance with a formula.

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
602
Trying to use this formula however I am getting an "error with formula". =IFERROR(AVERAGEIF(K25:O25,”>0”),”0”)
Maybe someone can lend me a hand with this?

Thanks,
PuJo
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Seba Robles

New Member
Joined
May 16, 2018
Messages
28
Try this

=IFERROR(IF(AVERAGE(K25:O25)>0,AVERAGE(K25:O25),0),0)

IF (Logical Condition: If Average is greater than 0, Value if Condition is Met then calculate average of specified range, value if condition not met give me 0

IF ERROR will automatically result in 0 if there is some error elsewhere
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,108
You'll need to use double quotes instead of the left and right double quotation marks...

=IFERROR(AVERAGEIF(K25:O25,">0"),"0")
 
Last edited:

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
602
Hey Seba, thanks for the reply however it doesn't seem to be ignoring the zero values in the data I am trying to average.
 

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
602
Thanks Dom! That did the trick!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,108
You're very welcome. By the way, while IFERROR returns 0 when AVERAGEIF returns an error, the 0 is formatted as text instead of a numerical value. To return a numerical value, simply remove the double quotes...

=IFERROR(AVERAGEIF(K25:O25,">0"),0)
 

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
602
Having the same problem again, this time I am averaging a named range. =AVERAGEIF(SS_FOURTH_Q,">0")
Tried =IFERROR(AVERAGEIF(SS_FOURTH_Q,">0"),0) as well as =IFERROR(AVERAGEIF(SS_FOURTH_Q,">0"),"0")
still getting the error.....

For some reason, my Mac keeps using the wrong quotes...

Thanks!

 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,108
I don't have a Mac, but on my keyboard the double quotes is located together with the apostrophe key. So I would press the key combination Shift + ' . If this is the only key available for double quotes, the problem might be with the system settings. In this case, check System Preferences >> Keyboard >> Text, something like that.
 
Last edited:

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
602
Yea, same on a Mac however my problem is that I was typing them in notepad very large font (my eyes are bad) then copying them to excel.
Probably an issue copying from notepad to excel. Thanks for the assistance, you really helped my out here!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,548
Messages
5,415,176
Members
403,571
Latest member
twinlock

This Week's Hot Topics

Top