Find the lowest4 numbers of the last 6 in a range
Is there a formula that will look at the last 6 non blank number values in a
range, and return the average of the four lowest? The numbers are in a row that is 16 columns and the numbers are added per division weekly. Looks like this: 45 43 44 45 51 58 38 49 52 38 60 59 41 65 50 Where the large spaces are blank cells, in this example I am looking for the average of 59, 41, 38, 50. Ideas? |
Find the lowest4 numbers of the last 6 in a range
Will there *always* be at least 6 numbers in the range? If not, then what
should happen? -- Biff Microsoft Excel MVP "Pjmcc64" wrote in message ... Is there a formula that will look at the last 6 non blank number values in a range, and return the average of the four lowest? The numbers are in a row that is 16 columns and the numbers are added per division weekly. Looks like this: 45 43 44 45 51 58 38 49 52 38 60 59 41 65 50 Where the large spaces are blank cells, in this example I am looking for the average of 59, 41, 38, 50. Ideas? |
Find the lowest4 numbers of the last 6 in a range
Not always 6, but there will be at least 4. Once we get to 6, I only want to
take the average of 4. Basically, the 4 lowest of the most recent 6. "T. Valko" wrote: Will there *always* be at least 6 numbers in the range? If not, then what should happen? -- Biff Microsoft Excel MVP "Pjmcc64" wrote in message ... Is there a formula that will look at the last 6 non blank number values in a range, and return the average of the four lowest? The numbers are in a row that is 16 columns and the numbers are added per division weekly. Looks like this: 45 43 44 45 51 58 38 49 52 38 60 59 41 65 50 Where the large spaces are blank cells, in this example I am looking for the average of 59, 41, 38, 50. Ideas? |
Find the lowest4 numbers of the last 6 in a range
Assuming that A2:P2 contains the data, try the following formulas that
need to be confirmed with CONTROL+SHIFT+ENTER... =AVERAGE(SMALL(INDEX(A2:P2,LARGE(IF(A2:P2<"",COLU MN(A2:P2)-COLUMN(A2)+1) ,6)):P2,{1,2,3,4})) or =IF(COUNT(A2:P2)=6,AVERAGE(SMALL(INDEX(A2:P2,LARG E(IF(A2:P2<"",COLUMN(A 2:P2)-COLUMN(A2)+1),6)):P2,{1,2,3,4})),"Less than 6 numbers") -- Domenic http://www.xl-central.com In article , Pjmcc64 wrote: Not always 6, but there will be at least 4. Once we get to 6, I only want to take the average of 4. Basically, the 4 lowest of the most recent 6. "T. Valko" wrote: Will there *always* be at least 6 numbers in the range? If not, then what should happen? -- Biff Microsoft Excel MVP "Pjmcc64" wrote in message ... Is there a formula that will look at the last 6 non blank number values in a range, and return the average of the four lowest? The numbers are in a row that is 16 columns and the numbers are added per division weekly. Looks like this: 45 43 44 45 51 58 38 49 52 38 60 59 41 65 50 Where the large spaces are blank cells, in this example I am looking for the average of 59, 41, 38, 50. Ideas? |
Find the lowest4 numbers of the last 6 in a range
Domenic wrote...
.... =AVERAGE(SMALL(INDEX(A2:P2,LARGE(IF(A2:P2<"", COLUMN(A2:P2)-COLUMN(A2)+1),6)):P2,{1,2,3,4})) .... You could shorten that to =AVERAGE(SMALL(INDEX(2:2, LARGE(COLUMN(A2:P2)*ISNUMBER(A2:P2),6)):P2,{1;2;3; 4})) |
All times are GMT +1. The time now is 10:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com