HIDE #VALUE! ERROR

[shickey]

Hi,

I have a simple formula in a cell which "=SUM(I26*H26)" but when the 126 & H26 cells are empty, I get the #VALUE! error in the box. I tried conditional formating but it does not work.

Can anyone help solve this little pain ?



Thanks

shickey

 

[Tazguy37]

In Excel 2000, I get 0 as a result.

 

[Brian from Maui]

Hi,

I have a simple formula in a cell which "=SUM(I26*H26)" but when the 126 & H26 cells are empty, I get the #VALUE! error in the box. I tried conditional formating but it does not work.

Can anyone help solve this little pain ?



Thanks

shickey

What's in I26 and H26?

 

[shickey]

i26 = "=VLOOKUP(D26,SpecData,5,FALSE)"

H26 is blank cell

 

[Brian from Maui]

i26 = "=VLOOKUP(D26,SpecData,5,FALSE)"

H26 is blank cell

When D26 is empty, do you get a #N/A error message?

 

[listerheb]

try this =if(I26="","",SUM(I26*H26))

 

[listerheb]

oops

I meant this if(H26="","",SUM(I26*H26))

If there is info in only H26 the result will be 0 that should be easy to conditionaly format.

 

[pulse_driver]

try this, its not very elegant but it will set the cell value to blank if there is ANY error (whether it be N/A, VALUE, NAME etc), this way you wont have to write a formula then also conditionally format the cell

=IF(ISERROR(SUM(I26*H26)),"",SUM(I26*H26))

 

[Aladin Akyurek]

i26 = "=VLOOKUP(D26,SpecData,5,FALSE)"

H26 is blank cell

A #VALUE! error would be diagnostic of a non-number value in H26... That said:

=IF(ISNUMBER(I26),I26*N(H26),"")

 

[shickey]

Thank you both. They both work well. Actually, put them both in the sheet to see what will happen down the road.


You both be the man



Thanks
ray:
shickey