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]

Do the relevant ranges contain error values, which would justify having ISERROR? And what is the Excel version you use?

 

[lhk201]

I am using excel 2003

$c$4 is numeric

h range is text

n range is numeric or blank

thank you

 

[T. Valko]

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!

Well, for starters we can get rid of a bunch of ( )...

=IF(ISERROR(SUMIF(Red!$H$2:$H$1000,$B21,Red!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

You also have some ranges that aren't the same size.

Now, I'm not sure why you're trapping errors that might come from the SUMIFs. Typically, they don't return errors.

You will get an error if C4 is an empty cell or if it contains numeric 0.

See if this does what you want:

=IF($C$4=0,"",(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)

 

[Aladin Akyurek]

I am using excel 2003

$c$4 is numeric

h range is text

n range is numeric or blank

thank you

Try...

=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,"")

 

[lhk201]

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?

 

[T. Valko]

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?

What version of Excel are you using?

 

[lhk201]

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!!!

 

[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!!!

OK, try this version...

=IF($C$4=0,"",IF(SUMIF(Red!$H$2:$H$1000,$B21,Red!N$2:N$1000)+SUMIF(Blue!$H$2:$H$1000,$B21,Blue!$N$2:$N$1000)=0,"",(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))

 

[Aladin Akyurek]

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!!!

Either...

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

Or...

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

There is no need for an IsEroor check per se.