Formula Help

B

brickman

Hi.

I need help with two scenarios.

1) Here is the formula
=SUMIF($L$7:$L$22,"=1",$D$7:$D$22)/SUMIF($L$7:$L$22,"=1",$C$7:$C$22).
Using this formula, how can I prevent it from giving me a "DIV/0" erro
if one the D or C = 0. I read somewhere that if I wrap it in a
=IF(ISERROR that would work but I cannot figure it out; if that's th
case, can you show me exactly how.

2) If I use a formula but want the number to always end in 900, ho
would I do this? i.e. 853,540 would be 853,900 or 1,232,320 would b
1,232,900.

If any of the above is too vague, please let me know and I will try t
explain further.

Thanks!!

Terr
 
M

mickbarry

Hi Brickman,
Part 1 of your question
Try the formula
=IF(ISERROR(SUMIF($L$7:$L$22,"=1",$D$7:$D$22)/SUMIF($L$7:$L$22,"=1",$C$7:$C$22)),"",SUMIF($L$7:$L$22,"=1",$D$7:$D$22)/SUMIF($L$7:$L$22,"=1",$C$7:$C$22)).

Part 2 of your question
Try the formula =LEFT(A1,LEN(A1)-3)&"900"

Can anyone answer my "Get external data" question?

Regards Mick Barry
If you need a bricklayer then get your chickens to eat clay.
 
Top