Hi,
I’ve got a spreadsheet with a player number and points. I’d like to return the player number which is closest to 50. If there is more than one value, I’d like it to display both (or if not possible pick randomly)!
Thanks,
Naomi
Idea: subtract 50 from the points of each player and find the minimum of the absolute value.
Add a column C to compute abs(points-50) : C4= ABS(B4-50)
in D1 (for example) compute the min of column C : D1 = min(C4:)
You get the player number with the formula : = LOOKUP(D1;C4:C8;A4:A8) which search for the value in D1 in the array C4:C8 and return the corresponding value in array A4:A8
and the corresponding points number with the formula : = LOOKUP(D1;C4:C8;B4:B8)
Read the help of the LOOKUP function.
(What a strange idea to attach an image in such a case instead of the spreadsheet file. It’s hard to make use of and it’s also the greater file size. Your image was 121 KB, my file is 15KB.)
Helper columns are a good idea generally. In this case also a solution without a helper column might be advisable if no additional access to the differebces is needed elsewhere. See this attached example for a demonstration. (An image wouldn’t be of any use.)
A hint in addition: You need to decide how to handle cases where more than one entry would be equally good matches, but only the first one is found. You also need to decide if the first match concerning the absolute difference shall be accepted or if you prefer another match with the opposite difference (+1 e.g. instead of -1).