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" error
if one the D or C = 0. I read somewhere that if I wrap it in an
=IF(ISERROR that would work but I cannot figure it out; if that's the
case, can you show me exactly how.

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

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

Thanks!!

Terry


--
brickman
------------------------------------------------------------------------
brickman's Profile: http://www.excelforum.com/member.php...o&userid=26912
View this thread: http://www.excelforum.com/showthread...hreadid=507017

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.


--
mickbarry
------------------------------------------------------------------------
mickbarry's Profile: http://www.excelforum.com/member.php...o&userid=31028
View this thread: http://www.excelforum.com/showthread...hreadid=507017