Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'conditional format' cells for the highest number in a range | Excel Discussion (Misc queries) | |||
how can i choose the highest number in excel range | Excel Worksheet Functions | |||
return rating of which number is highest and descending in column | Excel Worksheet Functions | |||
how do I change the font of highest number in a range of cells | New Users to Excel | |||
How do I return the highest value in a range of cells | Excel Worksheet Functions |