I am trying to average only the last 5 nonblank values in a row of cells. Is this possible?
T tmsciulli Apr 29, 2007 #1 I am trying to average only the last 5 nonblank values in a row of cells. Is this possible?
G Guest Apr 29, 2007 #2 Use this custom function call with =AverageLast5("Q") where Q is the column Note: if there are less than 5 items in the row it will average only the number of cells with values. Function AverageLast5(MyCol As String) LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row RowCount = LastRow CellCount = 0 Total = 0 Do While (RowCount > 0) And (CellCount <> 5) If Not IsEmpty(Cells(RowCount, MyCol)) Then Total = Total + Cells(RowCount, MyCol) CellCount = CellCount + 1 End If RowCount = RowCount - 1 Loop AverageLast5 = Total / CellCount End Function
Use this custom function call with =AverageLast5("Q") where Q is the column Note: if there are less than 5 items in the row it will average only the number of cells with values. Function AverageLast5(MyCol As String) LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row RowCount = LastRow CellCount = 0 Total = 0 Do While (RowCount > 0) And (CellCount <> 5) If Not IsEmpty(Cells(RowCount, MyCol)) Then Total = Total + Cells(RowCount, MyCol) CellCount = CellCount + 1 End If RowCount = RowCount - 1 Loop AverageLast5 = Total / CellCount End Function
G Guest Apr 29, 2007 #3 =MID(IF(ROW(A1:A30)>=LARGE(IF(ISNUMBER(A1:A30),IF(A1:A30>0,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,">0"))),IF(A1:A30>0,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER "(e-mail address removed)" skrev:
=MID(IF(ROW(A1:A30)>=LARGE(IF(ISNUMBER(A1:A30),IF(A1:A30>0,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,">0"))),IF(A1:A30>0,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER "(e-mail address removed)" skrev:
G Guest Apr 29, 2007 #4 the functon will not work if the total last value in the same column as the data. I'm search for the last value in the row. If you want the total to be at the end of the data then change from: LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row to: LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row - 1
the functon will not work if the total last value in the same column as the data. I'm search for the last value in the row. If you want the total to be at the end of the data then change from: LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row to: LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row - 1
G Guest Apr 29, 2007 #5 put 5 in cell B1 "excelent" skrev: =MID(IF(ROW(A1:A30)>=LARGE(IF(ISNUMBER(A1:A30),IF(A1:A30>0,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,">0"))),IF(A1:A30>0,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER "(e-mail address removed)" skrev: Click to expand...
put 5 in cell B1 "excelent" skrev: =MID(IF(ROW(A1:A30)>=LARGE(IF(ISNUMBER(A1:A30),IF(A1:A30>0,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,">0"))),IF(A1:A30>0,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER "(e-mail address removed)" skrev: Click to expand...
G Guest Apr 29, 2007 #6 Perhaps AVERAGE instead of MID?! excelent said: put 5 in cell B1 "excelent" skrev: Click to expand...
Perhaps AVERAGE instead of MID?! excelent said: put 5 in cell B1 "excelent" skrev: Click to expand...
G Guest Apr 29, 2007 #7 UPS ur right Toppers =AVERAGE(IF(ROW(A1:A30)>=LARGE(IF(ISNUMBER(A1:A30),IF(A1:A30>0,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,">0"))),IF(A1:A30>0,A1:A30))) "Toppers" skrev:
UPS ur right Toppers =AVERAGE(IF(ROW(A1:A30)>=LARGE(IF(ISNUMBER(A1:A30),IF(A1:A30>0,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,">0"))),IF(A1:A30>0,A1:A30))) "Toppers" skrev:
G Guest Apr 29, 2007 #8 another way =AVERAGE(LARGE(A1:A3000,ROW(INDIRECT("1:" & 5)))) hit ctrl+shift+enter returns average of last 5 cells in range A1:A3000 "excelent" skrev:
another way =AVERAGE(LARGE(A1:A3000,ROW(INDIRECT("1:" & 5)))) hit ctrl+shift+enter returns average of last 5 cells in range A1:A3000 "excelent" skrev:
G Guest May 1, 2007 #9 No it doesn't: it's the largest 5 values! excelent said: another way =AVERAGE(LARGE(A1:A3000,ROW(INDIRECT("1:" & 5)))) hit ctrl+shift+enter returns average of last 5 cells in range A1:A3000 "excelent" skrev: Click to expand...
No it doesn't: it's the largest 5 values! excelent said: another way =AVERAGE(LARGE(A1:A3000,ROW(INDIRECT("1:" & 5)))) hit ctrl+shift+enter returns average of last 5 cells in range A1:A3000 "excelent" skrev: Click to expand...
