return next highest number in range
 

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.

return next highest number in range
 

One way...

Try this array formula** :

=MIN(IF(A2:A10MAX(B2:B10),A2:A10))

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

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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.

return next highest number in range
 

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

"T. Valko" wrote:

One way...

Try this array formula** :

=MIN(IF(A2:A10MAX(B2:B10),A2:A10))

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

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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.

return next highest number in range
 

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:A6MAX(B1:B6),A1:A6))

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

A1:A6MAX(B1:B6)

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

MAX(B1:B6) = 4

A14 = FALSE
A24 = FALSE
A34 = FALSE
A44 = FALSE
A54 = TRUE
A64 = 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.

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

One way...

Try this array formula** :

=MIN(IF(A2:A10MAX(B2:B10),A2:A10))

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

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
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.

return next highest number in range
 

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

return next highest number in range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Code Numpty" wrote in message
...
Biff, Thanks for your time and trouble. A clear and concise explanation
really helps me to master things. I appreciate your help.

You're welcome. Thanks for the feedback!
 

On Thu, 03 Mar 2011 00:58:51 GMT, Santiago Castro wrote:

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:$4A8),$4:$4)