Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for the last higher value from a list?
Does anyone know how to search for the last higher value from a list? for
example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 45, which is the last value from the lists, and return 50 on cell B8, since 45 is higher than 20,30,40, until 45 is less than 50, therefore, the result is less than 50. Does anyone know how to code it in excel? Thank you in advance Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for the last higher value from a list?
huh?
Why return 50? I think you need to rephrase your explanation. This formula will return the last value in an array that is less than a target value (50): =LOOKUP(2,1/(A1:A8<50),A1:A8) Biff "Eric" wrote in message ... Does anyone know how to search for the last higher value from a list? for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 45, which is the last value from the lists, and return 50 on cell B8, since 45 is higher than 20,30,40, until 45 is less than 50, therefore, the result is less than 50. Does anyone know how to code it in excel? Thank you in advance Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for the last higher value from a list?
Thank you for your reply, I would describe in more details with follwoing
examples for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 45, which is the last value from the lists, and return 50 on cell B8, since 45 is higher than 20,30,40, until 45 is less than 50, therefore, the result is 50. for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 75, which is the last value from the lists, and return 75 on cell B8, since 75 is higher than 20,30,40,50,60 until 75 is equal to 75 in [A2], therefore, the result is 75. for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 101, which is the last value from the lists, and return 100 on cell B8, since 100 is highest value from the upper lists, therefore, the result is 100. Does anyone know how to code it in excel? Thank you in advance Eric |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for the last higher value from a list?
Try this formula in B2, Drag/Fill down as needed:
=IF( COUNTIF( $A$1:$A1, "" & $A2 ) = 0, MAX( $A$1:$A1 ), INDEX( $A$1:$A1, SUMPRODUCT( MAX( ( $A$1:$A1 = $A2 ) * ROW( $A$1:$A1 ) ) ) ) ) -- Regards, Luc. "Festina Lente" "Eric" wrote: Does anyone know how to search for the last higher value from a list? for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 45, which is the last value from the lists, and return 50 on cell B8, since 45 is higher than 20,30,40, until 45 is less than 50, therefore, the result is less than 50. Does anyone know how to code it in excel? Thank you in advance Eric |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for the last higher value from a list?
This formula returns the expected results of your samples:
sample1 = 50 sample2 = 75 sample3 = 100 Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(A8=MAX(A1:A8),LARGE(A1:A8,2),MIN(IF(A1:A7=A8, A1:A7))) Biff "Eric" wrote in message ... Thank you for your reply, I would describe in more details with follwoing examples for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 45, which is the last value from the lists, and return 50 on cell B8, since 45 is higher than 20,30,40, until 45 is less than 50, therefore, the result is 50. for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 75, which is the last value from the lists, and return 75 on cell B8, since 75 is higher than 20,30,40,50,60 until 75 is equal to 75 in [A2], therefore, the result is 75. for example, [A1] 100 [A2] 75 [A3] 60 [A4] 50 [A5] 40 [A6] 30 [A7] 20 [A8] 101, which is the last value from the lists, and return 100 on cell B8, since 100 is highest value from the upper lists, therefore, the result is 100. Does anyone know how to code it in excel? Thank you in advance Eric |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to search for the last higher value from a list?
Thank everyone very much
Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto updating list | Excel Worksheet Functions | |||
Search multiple columns and display corresponding cells in a list?!? | Excel Discussion (Misc queries) | |||
Excel list feature should search with more than first character | Excel Worksheet Functions | |||
Search for most recent date from list of dates | Excel Discussion (Misc queries) | |||
Using advanced filter to search for criteria in a list | Excel Discussion (Misc queries) |