| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I calculate an average of the last 5 nonblank cells in a row?
I am trying to average only the last 5 nonblank values in a row of
cells. Is this possible? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in a ro
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 " wrote: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in a ro
=MID(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30),IF( A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,"0") )),IF(A1:A300,A1:A30)))
INSERT AND HIT CTRL+SHIFT+ENTER " skrev: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in
put 5 in cell B1
"excelent" skrev: =MID(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30),IF( A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,"0") )),IF(A1:A300,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER " skrev: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in
Perhaps AVERAGE instead of MID?!
"excelent" wrote: put 5 in cell B1 "excelent" skrev: =MID(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30),IF( A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,"0") )),IF(A1:A300,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER " skrev: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in
UPS ur right Toppers
=AVERAGE(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30) ,IF(A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30," 0"))),IF(A1:A300,A1:A30))) "Toppers" skrev: Perhaps AVERAGE instead of MID?! "excelent" wrote: put 5 in cell B1 "excelent" skrev: =MID(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30),IF( A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,"0") )),IF(A1:A300,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER " skrev: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in
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: UPS ur right Toppers =AVERAGE(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30) ,IF(A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30," 0"))),IF(A1:A300,A1:A30))) "Toppers" skrev: Perhaps AVERAGE instead of MID?! "excelent" wrote: put 5 in cell B1 "excelent" skrev: =MID(IF(ROW(A1:A30)=LARGE(IF(ISNUMBER(A1:A30),IF( A1:A300,ROW(A1:A30))),MIN(B1,COUNTIF(A1:A30,"0") )),IF(A1:A300,A1:A30))) INSERT AND HIT CTRL+SHIFT+ENTER " skrev: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I calculate an average of the last 5 nonblank cells in a ro
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 " wrote: I am trying to average only the last 5 nonblank values in a row of cells. Is this possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| tally of nonblank cells | Excel Worksheet Functions | |||
| Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
| counting nonblank cells | Excel Worksheet Functions | |||
| Excel cannot shift nonblank cells | Excel Discussion (Misc queries) | |||
| Adding NonBlank Cells | Excel Worksheet Functions | |||
Hybrid Mode
