We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How to find the closest value to 50? [closed]

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

naomibrown gravatar image

updated 2020-08-02 11:18:39 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-02 11:19:50.284101

2 Answers

Sort by » oldest newest most voted

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

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 +0200

Lupp gravatar image

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

(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

Question Tools

1 follower


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

Seen: 878 times

Last updated: Sep 01 '18