VLOOKUP Summing Wrong

piginabush

Board Regular
Joined
Mar 12, 2010
Messages
55
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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Well-known Member
Joined
Jan 15, 2010
Messages
697
I get 3.2 ... have you formatted the cell as numeric with zero dec places by any chance?
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
quite possibly :LOL:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,312
Members
409,862
Latest member
lbisacca
Top