• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

lookup & match unexpected results

M

marninei

New Member
Hello,

I have a list of numbers (specifically 1%-10% in whole numbers) arranged in ascending order and I wan't the find in what place is a certain % that I'm looking for.

It all works fine except for when I look for 6%, when I get the result for 5% (?!).
I checked this both with LOOKUP and MATCH (set at exact match).

I thought maybe it had to do with the 0.0000......00001 figure, but no, I even validated that the number in the list is equal to the lookup value (=A1=B6) and got TRUE.

Does anyone have an idea what can be the reason for this?

Thank you,
Oren
 
Hi Asheesh,

In the attached file you'll find that when the discount (F4) is 6% the result in F5 returns the total discount that correlates with a 5% discount, but for any other figure it returns the correct result.

Thank you,
Oren
 

Attachments

  • Lookup problem.xlsx
    27 KB · Views: 0
Hi Oren,

Use the below formula in Cell F5 and must be entered using CTRL +SHIFT + ENTER..then copy it down..

LOOKUP(10^10,IF($C$5:$C$14=$F$4,$E$5:$E$14))

Hope this helps..
 
Here is your attachment...made change in Just F5 and then used the cell reference..
 

Attachments

  • Lookup problem.xlsx
    24.6 KB · Views: 0
You welcome..

The problem definitely is not visible to my Naked eyes..but this is a wild guess, there is some rounding issue with the percentages in cell C10..what makes me say so...

Now, try the below

Put In Cell C6 = ROUND(C5+1%,2) and then drag it down to C14...and you will see the difference...

Note: retain the formula of your initial attachment...#3
 
Last edited:
This is very odd.

I thought so too so I checked the values and they were identical (as I mentioned I got TRUE when checking if one equals the other).

Also, I checked the effect of rounding up to the 200th decimal and they were all equal to 6%.

I can't understand why your solution worked (worked even when rounding to the 1000th decimal)...
 
Very odd... I did some test.
Changed F4 to following.
1) 0.06
2) C4+0.06
3) C4+0.01+0.01+0.01+0.01+0.01+0.01
4) C4+(0.01)*6

All tested TRUE to being equal to C10 = C9+0.01
But only 3) returned right value using LOOKUP. :confused:
 
Asheesh was correct in his guess, it's a decimal error within XL precision system caused by floating point arithmatic. Error is slight, but your original formulas in C5:C14 are all dependent on each other. So, a little error in C5 + little error in C6 + little error in C7 ... eventually caused value in C10 to be off enough that the LOOKUP failed.

Alternative approach:
In C4, put this formula, copy down
=ROWS(C$4:C4)/100

Now each formula is independent, and error shouldn't appear.

If you really want to dig into error (or learn a bit), here's some good links:
https://support.microsoft.com/en-us/kb/214118
http://www.cpearson.com/excel/rounding.htm

Short answer: Computers use binary at their ultimate base, and this causes problems when doing precision math.
 
Hi Luke,

Thanks for your response, I will definitely look into these links BUT... note that even if you just put 5% as a value in C9 (disabling the aggregated errors) the error occurs.

Anyways, unless anyone feels like digging deeper into this issue I think we have dug deep enough.

Thank you all,
Oren
 
Hi Oren,

Reading more, and I'm not a floating point expert ;), but I think the problem is that when the formula to get the 6% was dependent on cell above (5%), the arithmatic was rounding the .05 some direction (I'm guessing up), and thus causing everything to be off.
with my formula:
upload_2015-9-1_10-56-31.png
 
I think you are right.
Just to keep a good thing going (let me know when you've had enough :DD) i put in C10 =5%+1% and it still rounded up.

Since neither 5% or 1% give an incorrect result on their own, it means that specifically the aggregate of 5% and 1% is the problem.
 
You must log in or register to reply here.
Back
Top