CODEWITHSUNDEEP
excelexcel-formula
Teiv
Teiv

Reputation: 2635

Find the nearest number greater than a specific value among cells

I have 3 different cells like this in my Excel table:

[B1] = 2
[N50] = 5
[V25] = 10

I want to find in those cells the nearest number greater than a specific value. For example, if the value is 1 then the number should be 2 in cell [B1]. If the value is 4 then the number should be 5 in cell [N50].

Upvotes: 0

Views: 488

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

=IF(A2<LARGE(CHOOSE({1,2,3},D2,F3,H1),1),
  IF(A2<LARGE(CHOOSE({1,2,3},D2,F3,H1),2),
  IF(A2<LARGE(CHOOSE({1,2,3},D2,F3,H1),3),
LARGE(CHOOSE({1,2,3},D2,F3,H1),3),
LARGE(CHOOSE({1,2,3},D2,F3,H1),2)),
LARGE(CHOOSE({1,2,3},D2,F3,H1),1)),
"larger or equal to all searched numbers")

and in one nice straight line for copy and paste purposes:

=IF(A2<LARGE(CHOOSE({1,2,3},D2,F3,H1),1),  IF(A2<LARGE(CHOOSE({1,2,3},D2,F3,H1),2),  IF(A2<LARGE(CHOOSE({1,2,3},D2,F3,H1),3), LARGE(CHOOSE({1,2,3},D2,F3,H1),3), LARGE(CHOOSE({1,2,3},D2,F3,H1),2)), LARGE(CHOOSE({1,2,3},D2,F3,H1),1)), "larger or equal to all searched numbers")

The hardest part for me and I did not understand it and still do not...just accepting, is building your separate cells into an array. That part is done by the CHOOSE():

CHOOSE({1,2,3},D2,F3,H1)

Once it is in an array, perform a large function to sort it from largest to smallest. Then through a series of nested IF statements, checked to see if the specified number in A2 was smaller than the K largest number.

Here is a proof of concept scaled down to smaller range but give you the idea.

Proof of Concept

Now I did it with a large, you could have done it with a small function as well, you would just need to alter your logic in the if statements.

This is an alternative approach without the if statements:

=IF(A2>=MAX(A7,C8,E6),"Greater than or equal to all options",INDEX(CHOOSE({1,2,3},A7,C8,E6),IFERROR(MATCH(A2,CHOOSE({1,2,3},A7,C8,E6),1)+1,1)))

Upvotes: 2

Related Questions