If formula question

[kluitna]

This seems very simple but for some reason I can not get it to work for me.

What I have it a Vlookup in a sereis of cells that ultimately are added together and summed.

In the column that adds the values together I place the following if formula:
=+IF(E9="#N/A",0,D9*E9)

The idea being if there is no value for the lookup table to lookup up it places a #N/A in that cell, which in turn wreaks havoc on my addition cell and well screws the column summing all up. so I thought I might out smart excel and tell it to place a zero in the addition column when ever it sees the #n/a, this did not work , it just place #n/a in the addition cell. I don't know why it did not work. Anyone out there know where I am going wrong here?

Thanks in advance

 

[Mark W.]

=IF(ISNA(E9),0,D9*E9)

 

[daleyman]

don't know if this is a typo, but equations start

=IF...

and not

=+IF...

Anyway, I normall adjust the VLOOKUP to take care of this in one go... using the TYPE function that returns value 16 in the case of an error...

for example...


=VLOOKUP(A1,rangename,2,false)

...would become...

=IF(TYPE(VLOOKUP(A1,rangename,2,false))=16,0,VLOOKUP(A1,rangename,2,false))

...or you could substitute this 0 value for a text string like "-" for display purposes, etc... alternatively, in the cell you were trying to put your original solution, you could use...

=IF(TYPE(E9)=16,0,D9*E9)