Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Dear expert,
Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Try the SMALL & LARGE functions.
-- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Ozgrid,
Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Elton,
Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Okay, assuming that the dates are always sequential, and there is no further
data below the table, try this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),)) If there is any data below the rows used for this table you would have to use this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),,COUN T($A$1:$A$8)-MATCH($D1,$A$1:$A$8,0))) Does this help? "Elton Law" wrote in message ... Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Should have mentioned: D1 is where I put =MIN($A$1:$A$8), and the formula
below would then go in D2, copied down as far as you require. "Steve Dunn" wrote in message ... Okay, assuming that the dates are always sequential, and there is no further data below the table, try this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),)) If there is any data below the rows used for this table you would have to use this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),,COUN T($A$1:$A$8)-MATCH($D1,$A$1:$A$8,0))) Does this help? "Elton Law" wrote in message ... Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi
Then for Lowest =MIN(A1:A8) or =SMALL(A1:A8,1) for the second lowest after the lowest (i.e the third lowest) then =SMALL(A1:A8,3) -- Regards Roger Govier Elton Law wrote: Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Roger (long time no speak),
I think Elton wanted to get the next minimum value in the list which was placed after the position of the initial minimum. Hmmmm, I now see his problem explaining! If (to simplify) we ignore the dates and round the figures down, we get this list: 21 35 36 18 57 67 26 24 The minimum in this case being 18. He now wants to igno 21 35 36 18 and produce the minimum from: 57 67 26 24 which is 24. Elton, please confirm that this is the case, if so then my formula will work, if not then we need further clarification. Cheers Steve D. "Roger Govier" wrote in message ... Hi Then for Lowest =MIN(A1:A8) or =SMALL(A1:A8,1) for the second lowest after the lowest (i.e the third lowest) then =SMALL(A1:A8,3) -- Regards Roger Govier Elton Law wrote: Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Steve
Good to "speak" to you again. CLUNK - sound of penny just dropping. Well worked out! -- Regards Roger Govier Steve Dunn wrote: Hi Roger (long time no speak), I think Elton wanted to get the next minimum value in the list which was placed after the position of the initial minimum. Hmmmm, I now see his problem explaining! If (to simplify) we ignore the dates and round the figures down, we get this list: 21 35 36 18 57 67 26 24 The minimum in this case being 18. He now wants to igno 21 35 36 18 and produce the minimum from: 57 67 26 24 which is 24. Elton, please confirm that this is the case, if so then my formula will work, if not then we need further clarification. Cheers Steve D. "Roger Govier" wrote in message ... Hi Then for Lowest =MIN(A1:A8) or =SMALL(A1:A8,1) for the second lowest after the lowest (i.e the third lowest) then =SMALL(A1:A8,3) -- Regards Roger Govier Elton Law wrote: Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Try these non volatile formulas:
In D1: =MIN(A1:A8) In D2: =MIN(IF((A1:A8D1)*(B1:B8VLOOKUP(D1,A1:B8,2,)),A1 :A8)) The first formula normally enter, the second formula press ctrl+shift+enter, not just enter. "Elton Law" wrote: Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Steve,
Thanks so much. You get exactly what I want. Thanks so much. I made a similar post asking for the highest and second highest. I just need to change Min to Max .... Both work very well. Your formula solved 2 posts.... Thanks so much ..... Thanks BTW, do you want to reply this post ... "Look for Max and second Max" The one just next to it. I can proclaim yours are the answer. "Steve Dunn" wrote: Should have mentioned: D1 is where I put =MIN($A$1:$A$8), and the formula below would then go in D2, copied down as far as you require. "Steve Dunn" wrote in message ... Okay, assuming that the dates are always sequential, and there is no further data below the table, try this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),)) If there is any data below the rows used for this table you would have to use this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),,COUN T($A$1:$A$8)-MATCH($D1,$A$1:$A$8,0))) Does this help? "Elton Law" wrote in message ... Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Teethless Mama,
Thanks for your reply. Yours work too. I have tried. I'm very much appreciated for your help. Thanks "Teethless mama" wrote: Try these non volatile formulas: In D1: =MIN(A1:A8) In D2: =MIN(IF((A1:A8D1)*(B1:B8VLOOKUP(D1,A1:B8,2,)),A1 :A8)) The first formula normally enter, the second formula press ctrl+shift+enter, not just enter. "Elton Law" wrote: Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look for Min and second Min
Hi Elton,
Have you considered what you want to happen if a figure is repeated in the list? Neither my nor Teethless Mama's solutions respond very well to this. The best single column solution I can see is: =MIN(OFFSET($A$1:$A$50,MAX(INDEX(($A$1:$A$50=N1)*R OW($A$1:$A$50),)),)) Unfortunately, this is still far from ideal. However, if you use D as a helper column, you can use the following: in D1: 0 in E1: =MIN($A$1:$A$50) from D1 to E1 and leave D1 blank, or put a zero in there. in D2: =MATCH($E1,OFFSET($A$1:$A$50,$D1,),0)+$D1 in E2: =MIN(OFFSET($A$1,$D2,,ROWS($A$1:$A$50)-$D2)) copy D2:E2 down as far as required. Tested with: 21 35 36 18 57 19 18 21 20 25 32 21 27 21 23 24 25 produces: 18 18 20 21 21 23 24 25 "Elton Law" wrote in message ... Hi Steve, Thanks so much. You get exactly what I want. Thanks so much. I made a similar post asking for the highest and second highest. I just need to change Min to Max .... Both work very well. Your formula solved 2 posts.... Thanks so much ..... Thanks BTW, do you want to reply this post ... "Look for Max and second Max" The one just next to it. I can proclaim yours are the answer. "Steve Dunn" wrote: Should have mentioned: D1 is where I put =MIN($A$1:$A$8), and the formula below would then go in D2, copied down as far as you require. "Steve Dunn" wrote in message ... Okay, assuming that the dates are always sequential, and there is no further data below the table, try this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),)) If there is any data below the rows used for this table you would have to use this: =MIN(OFFSET($A$1:$A$8,MATCH($D1,$A$1:$A$8,0),,COUN T($A$1:$A$8)-MATCH($D1,$A$1:$A$8,0))) Does this help? "Elton Law" wrote in message ... Hi Steve, In this case, I would to get the lowest (which is very easy) and then get the second lowest after the lowest is formed in date squenece. I get the result which are 18.25 on 9 Feb and 24.59 on 1 April 2010. In theory, the lowest is 18.25 and second min is 21.36 in the series. But I need the second low after the the first low is done. So the only second smallest after 18.25 should be 24.59. 21.36 should be ignored. Can any function be done this. thanks indeed for help. Thanks 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 "Steve Dunn" wrote: Hi Elton, Please explain the "sequent issue" "Elton Law" wrote in message ... Hi Ozgrid, Small and Large cannot solve the sequent issue. Thanks "ozgrid.com" wrote: Try the SMALL & LARGE functions. -- Regards Dave Hawley www.ozgrid.com "Elton Law" wrote in message ... Dear expert, Have posted a similar message before, but look like no one replied. Lat me try the last time as I found the previous post was not clear enough. I'm looking for the min and the second lowest (after the first min is found). In thie case, I hope I can use function to sort out 24.59 after the lowest 18.25 is formed. 18.25 is easy ... just the problem of 24.59 .... Thanks indeed. 21.36 22-Jan-10 35.04 28-Jan-10 36.34 02-Feb-10 18.25 09-Feb-10 57.50 19-Feb-10 67.52 02-Mar-10 26.85 18-Mar-10 24.59 01-Apr-10 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|