Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Largest Number Based on Criteria
I have a problem that is stumping me. In cells A1 through A10, I have numbers 1-10. In cells B1-B10 I have corresponding identifiers, Apples, Oranges, Pears. It looks like this A B 1 Apples 2 Apples 3 Oranges 4 Oranges 5 Apples 6 Pears 7 Oranges 8 Pears 9 Oranges 10 Apples I need a formula that I can copy down that looks in column B for "Oranges" and returns the corresponding number that is next largest above the number it already found. The result would look like below... 3 4 7 9 Any feedback would be most helpful. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Largest Number Based on Criteria
There may be an easier way, but this late into the night this is the method I came up with. Assuming your data is in cells A1:B10, you would enter this as an array formula (CTRL+Shift+Enter)into cell C1 and copy it down to cell C10.
=IF(COUNTIF($B$1:$B$10, "Oranges")= ROW(A1), SMALL(IF($B$1:$B$10="Oranges", $A$1:$A$10, 10^308), ROW(A1)), "") Good luck Ben |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Largest Number Based on Criteria
On Wednesday, February 27, 2013 9:02:32 PM UTC-8, Ben McClave wrote:
There may be an easier way, but this late into the night this is the method I came up with. Assuming your data is in cells A1:B10, you would enter this as an array formula (CTRL+Shift+Enter)into cell C1 and copy it down to cell C10. =IF(COUNTIF($B$1:$B$10, "Oranges")= ROW(A1), SMALL(IF($B$1:$B$10="Oranges", $A$1:$A$10, 10^308), ROW(A1)), "") Good luck Ben It works! Thank you Ben |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Largest Number Based on Criteria
No problem, I'm happy to help.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the Max Number in a Table based on Other Criteria | Excel Programming | |||
FInding the largest number in a range meeting a criteria | Excel Discussion (Misc queries) | |||
Finding the largest number help | Excel Discussion (Misc queries) | |||
Finding a number based on a criteria | Excel Worksheet Functions | |||
Finding the Largest Number, based on two criteria | Excel Discussion (Misc queries) |