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?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

GlennUK

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

Forum statistics

Threads
1,141,740
Messages
5,708,237
Members
421,553
Latest member
Geeyj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top