Last 5 Summary - sparse column

  • Thread starter Thread starter lb
  • Start date Start date
L

lb

I want to sum the last 5 entries in a column that have numbers in them. IE:
Rolling sum. I have tried the help text and Walkenbach's Excel 2003 Bible
and do not see a simple way without a macro. Any ideas?

What I really want to do is have a rolling average of the last five events,
when entered in the event.
The solution I came up with uses hidden collums and if the event is not
entered, the if statement in the cell carry's the last event forward, if the
event is entered is carry;s that event forward and moves across one column
the previous event (thus dropping the sixth last event). Then averages the
5 entries from the row. While this works, I wanted to know if anyone had a
better method. My spread sheet has 20 such rolling averages and it seems a
bit excessive to have 80 extra column's to keep track. Plus next year the
rules for the events might change to 7 or 10 events, etc.
event hidden columns average
97 97.60
97 97 0 0 0
95 97 97 0 0
99 95 97 97 0
100 99 95 97 97 97.60
94 100 99 95 97 97.00
94 100 99 95
98 94 100 99 95 97.20
 
lb said:
I want to sum the last 5 entries in a column that have numbers in them. IE:
Rolling sum.
Click to expand...

=COUNTIF(1:1,">0")

Get columns number of used range.
that function without count cells if not a number.

Than gat last cell.
=OFFSET(A1,COUNTIF(1:1,">0")-1,0,1,1)
 
Back
Top