VLOOKUP Summing Wrong

[piginabush]

Hi All,

Column J contains a VLOOKUP Number
Column K contains another VLOOKUP
Column L contains a SUM number, can be positive or negative

Column M contains the following formula;

=IF(L9<-0.5,K9*(ABS(L9)),"0")

Basically, I want to say if L9 is less than 0, return K9 times the absolute of L9, otherwise return 0.

However, the calculation is not coming up right, answers that should be right are not returning correct.

For example, I have L9 = minus 8, K9 = 0.4 and the answer being returned is 3.00 when 8 times 0.4 is 3.20?!?!?

Can anyone shed any light?

 

[njimack]

=IF(L9<-0.5,K9*(ABS(L9)),"0")

Basically, I want to say if L9 is less than 0

Your explanation doesn't tally with your formula. Maybe something like this:

=K9*IF(L9<0,ABS(L9),0)

 

[GlennUK]

Increase the number of decimal places in K9 and L9 to see if they really are -8 and 0.4 ... I doubt it, I'd guess that they are being rounded in the display to show those values, and that the actual values are different.

 

[WaterGypsy]

I get 3.2 ... have you formatted the cell as numeric with zero dec places by any chance?

 

[piginabush]

I get 3.2 ... have you formatted the cell as numeric with zero dec places by any chance?

quite possibly

what do I need them to be?

 

[GlennUK]

quite possibly

what do I need them to be?

That's not the point ... if the actual values in the cells are being multiplied correctly then it's only your perception of the numbers that is wrong. If you really want to round numbers before multiplying them then use the ROUND function ... it is only you that can decide what you really want to do with these numbers.