LinkBack URL
About LinkBacks
Can somebody have a quick look at the attached example and tell me why cells B11 & B12, and cells B22 & B23 are showing the same result instead of differing ones? Much obliged.
Can somebody have a quick look at the attached example and tell me why cells B11 & B12, and cells B22 & B23 are showing the same result instead of differing ones? Much obliged.
Last edited by Marvo; 09-01-2017 at 10:54 AM.
vlookup formulas find the first value that satisfies the condition. It appears the value in J13 matches the value in J14 so it will return the first instance it finds.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
But the names are different in Column M, I thought the formula in column K was taking that into consideration? I've never had this happen before, not sure what I've got wrong.
I guess I'm asking, why are the values in J13 & J14 matching if there are different names in Column M?
unfortunately your formula refers to a table and my iMac does not handle excel well so I am having a hard time finding the table parameters. Sorry I don't think I can add much any more on this.
Thanks anyway.
Are you sure about J13 and J14 "matching"? When I download your sheet, J13 is different from J14, and the lookup is correctly returning different results.
The interesting ones are J10, J11, J12, and J13, since they all appear to be the same -0.017 value. If you expand columns J and K (from which the values in J are taken) and expand the number format to include 20 digits, you can see some "floating point error" in these values. I cannot fully explain it, but I would guess that, deep in the binary representation of each of these numbers (beyond the 15 digits that Excel displays), there are 3 different -0.017 values among these four entries. J10 is almost certainly smaller than J11 which is smaller than J12, and J12 and J13 appear to be the same. That's as much explanation as I can give without having a way to see behind those numbers into how Excel and your CPU are storing those numbers.
Others (joeu2004 in particular) may have more insight to offer.
If you are interested, I have a few links here that discuss floating point errors: https://www.excelforum.com/groups/ma...nd-errors.html
Originally Posted by shg
Thank you. I use this sort of thing a lot, never had a problem before.
Mr Shorty, that is interesting, I tried to expand the cell results and went out pretty far and couldn't see it but as I noted, the iMac doesn't handle excel like a pc does.Thanks for weighing in to find the issue.
Sadly, still doesn't solve my problem.
Change K2 to:
=SUM(O2:T2)+1/(ROW()*10^10)
If this is NOT what you want, please explain what the original formula in K2 was SUPPOSED to be doing...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Thanks Glenn. That works in this instance but not what I wanted.
What the original formula usually does is exactly what your formula did in this instance for Column "O" but for whatever reason it didn't do it this time. The number in Column "O" is a points total. If the points are the same then the formula looked at Column "Q" to decide which was the greater. If still equal then column "P" & so on. At the start before any numbers were entered it sorted alphabetically from Column "M".
So, whilst your formula works for that one particular column, it wont work when the other columns are filled, it just adds the whole row up.
Simplified, Column "Q" is a decider, not to be added to Column "O".
I have workbooks that show this working but they're are too big for on here.
I've solved it!
The formula in Cell K2 was =O2/1000+Q2/1000^2+R2/1000^3+S2/1000^4+COLUMN(M2)/1000^5-ROW(K2)/1000^6
It should have been =O2/1000+Q2/1000^2+R2/1000^3+S2/1000^4+COLUMN(M2)/1000^5-ROW(K2)/1000^5
It now works as it was intended and how it has done up to now.
For anybody interested I've attached the working work book.
Thanks for all your help, sorry to have wasted anybodies time. One number out, that's all it takes.
Glad you got sorted. It does seem a bit complicated... but if it works for you.You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks