If formula question

kluitna · Apr 3rd, 2002, 01:29 PM

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. · Apr 3rd, 2002, 01:31 PM

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

daleyman · Apr 3rd, 2002, 01:34 PM

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)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 01:29 PM	#1
kluitna Board Regular Join Date: Mar 2002 Posts: 75	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

Apr 3rd, 2002, 01:31 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=IF(ISNA(E9),0,D9*E9)

Apr 3rd, 2002, 01:34 PM	#3
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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)