DIV/0! error HELP :(

[craig80]

Hi Guys,

I am trying to return a blank from the below function, however my ISERROR doesn't seem to be working? Can anyone shead some light?

Thanks

BEFORE ERROR CHECK
=(SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20)))/(SUM(COUNTIFS(CA,">=10",Bus_Area,$B20),COUNTIFS(SA,">=10",Bus_Area,$B20),COUNTIFS(EA,">=10",Bus_Area,$B20),COUNTIFS(ENGA,">=10",Bus_Area,$B20),COUNTIFS(PLA,">=10",Bus_Area,$B20))+SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20)))

 

[Andrew Poulsom]

I don't see any ISERROR there, but why do you need it? If you don't want to see the result if it's zero format the cell as eg:

General;General;

 

[craig80]

It needs to return a % or a blank?

 

[Andrew Poulsom]

Sorry, I missed the division. Try:

=IF((SUM(COUNTIFS(CA,">=10",Bus_Area,$B20),COUNTIFS(SA,">=10",Bus_Area,$B20),COUNTIFS(EA,">=10",Bus_Area,$B20),COUNTIFS(ENGA,">=10",Bus_Area,$B20),COUNTIFS(PLA,">=10",Bus_Area,$B20))+SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20))),(SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20)))/(SUM(COUNTIFS(CA,">=10",Bus_Area,$B20),COUNTIFS(SA,">=10",Bus_Area,$B20),COUNTIFS(EA,">=10",Bus_Area,$B20),COUNTIFS(ENGA,">=10",Bus_Area,$B20),COUNTIFS(PLA,">=10",Bus_Area,$B20))+SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20))),"")

 

[craig80]

Fantastic,

thanks you.

I can't see what you changed? Can you highlight it for me, so I can learn something new today?

thanks

C

 

[Andrew Poulsom]

Sure:

=IF((SUM(COUNTIFS(CA,">=10",Bus_Area,$B20),COUNTIFS(SA,">=10",Bus_Area,$B20),COUNTIFS(EA,">=10",Bus_Area,$B20),COUNTIFS(ENGA,">=10",Bus_Area,$B20),COUNTIFS(PLA,">=10",Bus_Area,$B20))+SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20))),(SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20)))/(SUM(COUNTIFS(CA,">=10",Bus_Area,$B20),COUNTIFS(SA,">=10",Bus_Area,$B20),COUNTIFS(EA,">=10",Bus_Area,$B20),COUNTIFS(ENGA,">=10",Bus_Area,$B20),COUNTIFS(PLA,">=10",Bus_Area,$B20))+SUM(COUNTIFS(CA,"p",Bus_Area,$B20),COUNTIFS(SA,"p",Bus_Area,$B20),COUNTIFS(EA,"p",Bus_Area,$B20),COUNTIFS(ENGA,"p",Bus_Area,$B20),COUNTIFS(PLA,"p",Bus_Area,$B20))),"")

 

[craig80]

Hi Andrew,

forgot to say thanks for this one. saved my skin.