Ask Your Question

How to find the closest value to 50?

asked 2018-09-01 09:03:04 +0100

naomibrown gravatar image


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

image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-09-01 14:07:34 +0100

JBF gravatar image

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.

edit flag offensive delete link more

answered 2018-09-01 14:37:55 +0100

Lupp gravatar image

updated 2018-09-01 14:49:37 +0100

(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).

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-01 09:03:04 +0100

Seen: 88 times

Last updated: Sep 01 '18