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

  • Thread starter Thread starter tmsciulli
  • Start date Start date
T

tmsciulli

I am trying to average only the last 5 nonblank values in a row of
cells. Is this possible?
 
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
 
=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:
 
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
 
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...
 
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:
 
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:
 
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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I can't insert column in Excell file! Help please 2
Count NonBlank Rows with Criteria in another column 5
Get Last NonBlank Value 5
CELL EQUALS LAST NONBLANK CELL IN A COLUMN 5
count nonblank cells with conditions 2
How Can I Insert A Column in A Spreadsheet With This Error Message 1
summing the values in nonblank cells 2
Return Count for LAST NonBlank Cell in each Row 12

Back
Top