help with trapping error in IF function

**Suety** · 03-29-2010, 01:10 PM

Hi All,

I am trying to trap errors for the following formula in Excel 2003.

=IF(EL39>0,(+EL39/EI39),0)

Scenario:
in EL39 value is 0.159 so first condition returns TRUE;
in EI39 value is 0 so this part (+EL39/EI39) causes the #DIV/0!

In 2007, i was able to solve it with the following:

=IFERROR(IF(EL39>0,(+EL39/EI39),0),0)

but i need the equivalent to this formula in 2003.

Can anyone assist?

Thanks!

**NBVC** · 03-29-2010, 01:53 PM

Try

=IF(E139<=0,0,EL39/EI39)

**darkyam** · 03-29-2010, 03:30 PM

Shouldn't it just be
=IF(EI39=0,0,EL39/EI39), in case EI39 can be a negative number?

**NBVC** · 03-29-2010, 03:39 PM

I made assumption based on original OP formula...

**darkyam** · 03-29-2010, 03:42 PM

That was with EL39, not EI39.

**NBVC** · 03-29-2010, 03:43 PM

True... let the OP decide.

**Suety** · 03-29-2010, 05:40 PM

Originally Posted by NBVC

True... let the OP decide.

Ok maybe i should explain the calculation:

The formula is to calculate the average rate. EL39 is the Revenue and EI39 is the arrivals. So the general idea is:
IF(EL37>0,(+EL37/EI37),0)
if the revenue is more than 0, then divide the revenue by the arrivals. If it's not greater than 0 don't do the calculation and place 0 in the cell.

in one scenario:
EL39 is 0.159 and EI is 0

based on the above explanation would the same suggestions apply?

**darkyam** · 03-29-2010, 05:51 PM

Since your data type seems to preclude the possibility of a negative number in EI, either way works. The < is unnecessary, but it won't hurt anything.

**Suety** · 04-01-2010, 10:46 AM

Hi guys thanks for all the suggestions! I was able to get it working with the following formula:

=IF(EL128>0,IF(ISERROR(+EL128/EI128),0,(+EL128/EI128)),0)

Once again thanks!