commission report #NA errors

[Guest]

I put this formula in my comm report and when i format the other cells in the
collumn i get an #na error message. It says that not all the data is
available. I may not put the data in and would like to get rid of the error
(i dont think anything is wrong, the math is ok) and it is affecting my
totals.
=LOOKUP(E5,{3,3.15,3.25,3.35,3.45,3.55,3.65,3.75},{0.38,0.43,0.46,0.49,0.
52,0.56,0.59,0.62})*D5-H5

Again i appreciate all the help!!

 

[Domenic]

If you want the calculation to take place only when E5 contains a
value...

=IF(E5<>"",LOOKUP(E5,{3,3.15,3.25,3.35,3.45,3.55,3.65,3.75},{0.38,0.43,0.
46,0.49,0.52,0.56,0.59,0.62})*D5-H5,"")

If you want the calculation to take place only when D5, H5, and E5, all
contain data...

=IF((D5<>"")*(E5<>"")*(H5<>""),LOOKUP(E5,{3,3.15,3.25,3.35,3.45,3.55,3.65
,3.75},{0.38,0.43,0.46,0.49,0.52,0.56,0.59,0.62})*D5-H5,"")

Hope this helps!

 

[CLR]

Try wrapping your formula in an IF statement..........

=IF(ISNA(LOOKUP(E5,{3,3.15,3.25,3.35,3.45,3.55,3.65,3.75},{0.38,0.43,0.46,0.
49,0.52,0.56,0.59,0.62})*D5-H5),"",LOOKUP(E5,{3,3.15,3.25,3.35,3.45,3.55,3.6
5,3.75},{0.38,0.43,0.46,0.49,0.52,0.56,0.59,0.62})*D5-H5)

Vaya con Dios,
Chuck, CABGx3