return next highest number in range

[Code Numpty]

I have tried doing this with INDEX and MATCH but can't get my head round it.

I have 2 columns of data, containing blanks that are the result of an IF
formula.

40
100
100
200
400
600
800
1200
1600

The values will always be sorted from low to high. What I need to do is take
the max value from column B (400 in this case) and return the next highest
value from column A (600).

Grateful for any pointers on this.

 

[T. Valko]

One way...

Try this array formula** :

=MIN(IF(A2:A10>MAX(B2:B10),A2:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Code Numpty]

Bill, you're a star. Do you have the time to explain how this formula works?
I'm not experienced with arrays.

 

[T. Valko]

Let's use a small sample and see how this works.

.......A.....B
1....1........
2....2........
3...........3
4...........4
5....5.......
6....6.......

=MIN(IF(A1:A6>MAX(B1:B6),A1:A6))

This portion of the formula will return an array of either TRUE or FALSE:

A1:A6>MAX(B1:B6)

We're comparing the values in col A to the MAX value of col B. So:

MAX(B1:B6) = 4

A1>4 = FALSE
A2>4 = FALSE
A3>4 = FALSE
A4>4 = FALSE
A5>4 = TRUE
A6>4 = TRUE

Where this condition is TRUE the corresponding numbers from col A are then
passed to the MIN function:

MIN({FALSE;FALSE;FALSE;FALSE;5;6)

MIN ignores the logical FALSE and returns the smallest number of the array.

So, the final result of the formula is 5.

 

[Code Numpty]

Biff, Thanks for your time and trouble. A clear and concise explanation
really helps me to master things. I appreciate your help.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Santiago Castro]

Bill, I saw what you did for our friend app there and it's pretty impresive.

I need to solve something similar but more complicated:

I have the following data:

Aug-99 Jul-99 Jun-99 May-99 Apr-99 Mar-99 Feb-99


6.92 6.90 6.88 6.86 6.86 6.84 6.79

What I need is find next biggest number to 6.87, but I nee excel to look for it in order according to the date to which each number is related. So the formulea needs to go first to Feb-99, look at the number below (6.79) and compare it to 6.83. Since the 6.79 is below 6.83, then go to Mar-99 and do the same and so on until it finds 6.88 (under Jun-99). PLEASE HELP!!!

 

[Charabeuh]

Hello,

I assumed your dates (date format) are in A1:G1.
I assumed your values are in A2:G2.

This formula shoudl give you the month with the value just greater
than 6.87:

=MIN(IF(A2:G2=MIN(IF(A2:G2>6.87,A2:G2,"")),A1:G1,""))

This formula shoudl give you the value just greater then 6.87
(format the call with a date format) :

=LOOKUP(MIN(IF(A2:G2=MIN(IF(A2:G2>6.87,A2:G2,"")),A1:G1,"")),
A1:G1,A2:G2)


These formulas are array formulas. You should validate these formulas
with the combination of the three keystrokes CTRL+Shift+Enter instead
of using the single keystroke ENTER. The formula will be surrounded
by braces. Every time you edit this formula, you have to validate
it with CTRL+Shift+Enter.

Hope it will help you.






Santiago Castro a utilisé son clavier pour écrire :

 

[Charabeuh]

Charabeuh a émis l'idée suivante :

Hello,

I assumed your dates (date format) are in A1:G1.
I assumed your values are in A2:G2.

This formula shoudl give you the month with the value just greater
than 6.87:

Sorry the second formula is
=MIN(IF(A2:G2>6.87,A2:G2,"")) (array formula)

 

[Ron Rosenfeld]

I need to solve something similar but more complicated:

I have the following data:

Aug-99 Jul-99 Jun-99 May-99 Apr-99 Mar-99 Feb-99


6.92 6.90 6.88 6.86 6.86 6.84 6.79

What I need is find next biggest number to 6.87, but I nee excel to look for it in order according to the date to which each number is related. So the formulea needs to go first to Feb-99, look at the number below (6.79) and compare it to 6.83. Since the 6.79 is below 6.83, then go to Mar-99 and do the same and so on until it finds 6.88 (under Jun-99). PLEASE HELP!!!

If I may restate your problem, is it the same as:
Return the value in the rightmost column that is greater than 6.87
??

If that is the case:
Your number values are in Row 4
The 6.87 is in A8

You may try this formula: =LOOKUP(2,1/($4:$4>A8),$4:$4)