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?

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?

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?

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?

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?

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?

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?

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?

How do I calculate an average of the last 5 nonblank cells in
 

No it doesn't: it's the largest 5 values!

"excelent" wrote:

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?