|
Second lowest in 28
Thanks to 'Biff', I was able to get the lowest result based on the previous
28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW(1:6 8)),28)))} and this gave me the lowest in the range. |
Instead of using MIN, use SMALL:
This is the same as using MIN: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),1) For the second lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),2) For the third lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),3) Etc.,Etc.. These are also array formulas. Biff -----Original Message----- Thanks to 'Biff', I was able to get the lowest result based on the previous 28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)))} and this gave me the lowest in the range. . |
This works, but when I have multiple cells with the same entry.... (In my
first set, I have the same low number of 3226 - 3 times), I have to go and ask for the 4th smallest, to get the actual 2nd lowest entry. To cure this, would I have to put in multiple IF statements, or is there an easier way? "Biff" wrote: Instead of using MIN, use SMALL: This is the same as using MIN: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),1) For the second lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),2) For the third lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),3) Etc.,Etc.. These are also array formulas. Biff -----Original Message----- Thanks to 'Biff', I was able to get the lowest result based on the previous 28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)))} and this gave me the lowest in the range. . |
Well, that's how SMALL and LARGE work if there are
duplicates. What to do about it depends on what you're trying to do. How many "next lowest" values do you want to extract? Biff -----Original Message----- This works, but when I have multiple cells with the same entry.... (In my first set, I have the same low number of 3226 - 3 times), I have to go and ask for the 4th smallest, to get the actual 2nd lowest entry. To cure this, would I have to put in multiple IF statements, or is there an easier way? "Biff" wrote: Instead of using MIN, use SMALL: This is the same as using MIN: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),1) For the second lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),2) For the third lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),3) Etc.,Etc.. These are also array formulas. Biff -----Original Message----- Thanks to 'Biff', I was able to get the lowest result based on the previous 28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)))} and this gave me the lowest in the range. . . |
Try the following...
First define the following reference... Insert Name Define Name: Numbers Refers to: =INDEX(Sheet1!$W$1:$W$68,LARGE(IF(Sheet1!$W$1:$W$6 8<"",ROW(Sheet1!$W$1:$ W$68)-ROW(Sheet1!$W$1)+1),28)):Sheet1!$W$68 Then, try the following formulas... Y1: =SMALL(Numbers,1) Y2, copied down: =SMALL(IF((Numbers<"")*(1-ISNUMBER(MATCH(Numbers,$Y$1:Y1,0))),Numbers),1 ) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , "sac73" wrote: This works, but when I have multiple cells with the same entry.... (In my first set, I have the same low number of 3226 - 3 times), I have to go and ask for the 4th smallest, to get the actual 2nd lowest entry. To cure this, would I have to put in multiple IF statements, or is there an easier way? "Biff" wrote: Instead of using MIN, use SMALL: This is the same as using MIN: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),1) For the second lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),2) For the third lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),3) Etc.,Etc.. These are also array formulas. Biff -----Original Message----- Thanks to 'Biff', I was able to get the lowest result based on the previous 28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)))} and this gave me the lowest in the range. . |
Just the lowest, and the second lowest.... Problem is, although almost
impossible, all 28 could be the low number.... More often than not, there are 2 or 3 multiple 'lowest' results. "Biff" wrote: Well, that's how SMALL and LARGE work if there are duplicates. What to do about it depends on what you're trying to do. How many "next lowest" values do you want to extract? Biff -----Original Message----- This works, but when I have multiple cells with the same entry.... (In my first set, I have the same low number of 3226 - 3 times), I have to go and ask for the 4th smallest, to get the actual 2nd lowest entry. To cure this, would I have to put in multiple IF statements, or is there an easier way? "Biff" wrote: Instead of using MIN, use SMALL: This is the same as using MIN: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),1) For the second lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),2) For the third lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),3) Etc.,Etc.. These are also array formulas. Biff -----Original Message----- Thanks to 'Biff', I was able to get the lowest result based on the previous 28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)))} and this gave me the lowest in the range. . . |
In article ,
"sac73" wrote: Just the lowest, and the second lowest.... Problem is, although almost impossible, all 28 could be the low number.... More often than not, there are 2 or 3 multiple 'lowest' results. Have you tried the solution I offered? |
Works for me!
Biff -----Original Message----- In article <E4965B9D-9188-451D-B1C9- , "sac73" wrote: Just the lowest, and the second lowest.... Problem is, although almost impossible, all 28 could be the low number.... More often than not, there are 2 or 3 multiple 'lowest' results. Have you tried the solution I offered? . |
Domenic, just to FYI,
This OP first posted about a week ago. He has a range of cells 5 columns by 15 rows and within that range are random blank cells (formula blanks, ""). The OP wanted the MIN of the last 28 cells in the range that have a number in them. I gave him an Offset formula that puts the multi column multi row range into a single column range. That way, it would be easy to get the MIN. Biff -----Original Message----- In article <E4965B9D-9188-451D-B1C9- , "sac73" wrote: Just the lowest, and the second lowest.... Problem is, although almost impossible, all 28 could be the low number.... More often than not, there are 2 or 3 multiple 'lowest' results. Have you tried the solution I offered? . |
Thanks Biff!
Yes, I missed the OP's first post. Also, thanks for confirming for me that the solution I offered works. It's nice to know. :) In article , "Biff" wrote: Domenic, just to FYI, This OP first posted about a week ago. He has a range of cells 5 columns by 15 rows and within that range are random blank cells (formula blanks, ""). The OP wanted the MIN of the last 28 cells in the range that have a number in them. I gave him an Offset formula that puts the multi column multi row range into a single column range. That way, it would be easy to get the MIN. Biff -----Original Message----- In article <E4965B9D-9188-451D-B1C9- , "sac73" wrote: Just the lowest, and the second lowest.... Problem is, although almost impossible, all 28 could be the low number.... More often than not, there are 2 or 3 multiple 'lowest' results. Have you tried the solution I offered? . |
Actually the range is quite a bit bigger. It's from H4:K57, but with the
offset formula from Biff, it covers W4:W263. (H1:K3 are not used, and there is a break from H16:K17) I have tried your suggestion, but I am getting a #N/A in cell Y2. What am I doing wrong? "Domenic" wrote: Thanks Biff! Yes, I missed the OP's first post. Also, thanks for confirming for me that the solution I offered works. It's nice to know. :) In article , "Biff" wrote: Domenic, just to FYI, This OP first posted about a week ago. He has a range of cells 5 columns by 15 rows and within that range are random blank cells (formula blanks, ""). The OP wanted the MIN of the last 28 cells in the range that have a number in them. I gave him an Offset formula that puts the multi column multi row range into a single column range. That way, it would be easy to get the MIN. Biff -----Original Message----- In article <E4965B9D-9188-451D-B1C9- , "sac73" wrote: Just the lowest, and the second lowest.... Problem is, although almost impossible, all 28 could be the low number.... More often than not, there are 2 or 3 multiple 'lowest' results. Have you tried the solution I offered? . |
Something to ponder............
=IF(SMALL(therange,2)=MIN(therange),SMALL(therange ,4),SMALL(therange,2)) etc, etc........ Vaya con Dios, Chuck, CABGx3 "sac73" wrote in message ... This works, but when I have multiple cells with the same entry.... (In my first set, I have the same low number of 3226 - 3 times), I have to go and ask for the 4th smallest, to get the actual 2nd lowest entry. To cure this, would I have to put in multiple IF statements, or is there an easier way? "Biff" wrote: Instead of using MIN, use SMALL: This is the same as using MIN: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),1) For the second lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),2) For the third lowest: =SMALL(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)),3) Etc.,Etc.. These are also array formulas. Biff -----Original Message----- Thanks to 'Biff', I was able to get the lowest result based on the previous 28 cells... and I thought it would be easy to convert that array so that I could also get the second lowest in the same 28 cells. Not quite as easy as I thought. Can anyone help? The original was: {=MIN(W68:INDEX(W1:W68,LARGE(IF(W1:W68<"",ROW (1:68)),28)))} and this gave me the lowest in the range. . |
I'm not sure why you're getting #N/A. Or are you actually getting a
#NUM! error? In article , "sac73" wrote: Actually the range is quite a bit bigger. It's from H4:K57, but with the offset formula from Biff, it covers W4:W263. (H1:K3 are not used, and there is a break from H16:K17) I have tried your suggestion, but I am getting a #N/A in cell Y2. What am I doing wrong? |
| All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com