IS ERROR & IF Formula Help

[lhk201]

I am using the following formula and for some reason when the first if function is false the result is a 0 not a blank ("""") as stated in the formula. Is there a way to correct this? Maybe I need to change it?

=IF(ISERROR((SUMIF(Red!$H$2:$H$1000,$B21Red!N$2:N$1000))+(SUMIF(Blue!$H$2:$H$988,$B21,Blue!N2:N1000))),"",((SUMIF(Red!$H$2:$H$1000,$B21,Red!N$2:N$1000))+(SUMIF(Blue!$H$2:$H$988,$B21,Blue!N2:N1000))))/$C$4

Thanks!

 

[Aladin Akyurek]

Unfortunately none of these formula seem to work. Let me explain a little more

$C$4 = total number of accounts in area a (10 areas in total)

$H$2:$H$1000 = whether the area is a, b, c, d, e, f, g, h, i or j

N$2:N$1000 = numerica value associated to the account

$b$21 = text saying "area a"

so the formula needs to average only accounts within the range that are area a, however when there area no figures in any of the area a accounts the result should be blank and not 0 which is currenly showing.



Any suggestions?

By the way, I had a paren missing...

=IF($C$4,SUM(SUMIF(Red!$H$2:$H$1000,$B21,Red!N$2:N$1000),
    SUMIF(Blue!$H$2:$H$1000,$B21,Blue!$N$2:$N$1000))/$C$4,"")

You can custom format the formula cell as:

[=0]"";General

in order to hide a 0 result.

 

[T. Valko]

I am using 2003

I am trying to do the same for this formula:

=IF(ISERROR(SUM(C18:C19))/2,"",SUM(C18:C19)/2)

but it comes back as 0 when there is no value instead of being blank which is what I wanted. So I have the same problem with this one!!!

For that formula, try it like this...

=IF(SUM(C18:C19),SUM(C18:C19)/2,"")

Or...

=IF(SUM(C18:C19),AVERAGE(C18:C19),"")

Note that I'm assuming that a 0 is an otherwise invalid result.

For example:

C18 = -10
C19 = 10

The sum result is 0 but that would seem to be a vaild result if negative values are possible.

 

[lhk201]

Yippee its now all working, thanks everyone for their help!!

 

[lhk201]

Ah Biff, I see what your saying, your right sometimes the result could be a 0 as negative values are possible, is there any way round this?

 

[T. Valko]

Ah Biff, I see what your saying, your right sometimes the result could be a 0 as negative values are possible, is there any way round this?

Then use this version...

=IF(COUNT(C18:C19),SUM(C18:C19)/2,"")

Or...

=IF(COUNT(C18:C19),AVERAGE(C18:C19),"")

You can custom format the formula cell as:

[=0]"";General

Be aware that using this format, it simply "hides" the numeric 0.

The cell will still contain the numeric 0 you just won't see it.

If you have downstream calculations that might be adversely impacted due to zeros you may not want to use that format.