Returning a 0

[Tomo]

=(SUM(B20:D20))/SUM(B20:F20)

How do i get this formula to return zero if the fields are empty.

Thankyou

 

[VoG]

Perhaps

=IF(COUNT(B20:F20)=0,0,SUM(B20:D20)/SUM(B20:F20))

 

[Tomo]

Still get the DIV/0! using the formula you gave me

 

[T. Valko]

Still get the DIV/0! using the formula you gave me

Perhaps

=IF(COUNT(B20:F20)=0,0,SUM(B20:D20)/SUM(B20:F20))

That formula should work.

If you still get a #DIV/0! error do you have both positive and negative numbers in the range B20:F20 that sum to 0?

If you're using Excel 2007 or later:

=IFERROR(SUM(B20:D20)/SUM(B20:F20),0)

 

[Tomo]

Working in excel 2007 but will be running it on excel 2003 it seems to work now thanks

 

[T. Valko]

Working in excel 2007 but will be running it on excel 2003 it seems to work now thanks

OK, if you're using Excel 2003 then you can't use the IFERROR version.

Excel 2003 doesn't support that function.

 

[Tomo]

shame it works on my 2007 but your right when i go to save it i get the message about compatabilty any ideas

 

[T. Valko]

shame it works on my 2007 but your right when i go to save it i get the message about compatabilty any ideas

Let's try this one...

=IF(SUM(B20:F20)=0,0,SUM(B20:D20)/SUM(B20:F20))

 

[Tomo]

Works a treat thanks

 

[T. Valko]

Works a treat thanks

You're welcome. Thanks for the feedback!