Rolling Average

[Guest]

i am trying to average the smallest 7 numbers out of the last ten entered (i
can to work if all ten cells have data), how do i handle blank cells so i
still look at ten cells of data. The data is entered per day and if a day is
missed i want to ignore and average with the last ten days data was entered

 

[Biff]

Hi!

Is the range going down a column or across a row?

Biff

 

[Guest]

I 12 rows of data, each row requires the rolling average.

 

[RagDyer]

Since you stated that the missing days are *BLANK*, and *NOT* zero, with the
data list in Column A, try this *array* formula in B1, and copy down as
needed:

=AVERAGE(SMALL(A1:A10,ROW(INDIRECT({"1:7"}))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Biff]

Golf handicaps?

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=AVERAGE(SMALL(Z$2:INDEX(A$2:Z$2,LARGE(IF(A$2:Z$2<>"",COLUMN(A$2:Z$2)),10)),ROW($1:$7)))

Adjust the ranges to suit. I'm assuming that you enter new values at the end
of the range so make the range large enough to allow for additional entries
(future expansion).

Copy down as needed.

If this is for golf hdcp's, once you get to the next level, 10 lowest from
the last 20, you can use this same formula and simply change the 10 to 20
and change ROW($1:$7) to ROW($1:$10).

Oh, I almost forgot.....this assumes that there are enough entries to meet
the requirements (10 entries), to add some robustness you could add this to
the front of the above formula:

=IF(COUNT(A$2:Z$2)<10,"insufficient data",above_formula_here)

Biff

 

[Biff]

Hi!

You don't need the array brackets inside Indirect. In fact, you don't need
Indirect at all.

=AVERAGE(SMALL(A1:A10,ROW($1:$7)))

Also, this only averages based on the range size and not on the number of
values inside that range. This would be better suited if the range didn't
contain any empty cells.

Biff

 

[Biff]

Hi!

In fact, you don't need Indirect at all.

Well, that's not exactly true!

If you expect to insert rows within the range then Indirect will "lock in"
the rows. If there is no possibility of inserting rows, then Indirect is not
needed.

Biff

 

[RagDyeR]

On occasion, when I've had a "liquid" lunch, curly brackets and "things"
tend to appear in my posts from out of nowhere.

I declare to *not* imbibing my lunch yesterday, so I have no idea where they
came from, since they weren't anywhere in my formula test sheet!<bg>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi!

You don't need the array brackets inside Indirect. In fact, you don't need
Indirect at all.

=AVERAGE(SMALL(A1:A10,ROW($1:$7)))

Also, this only averages based on the range size and not on the number of
values inside that range. This would be better suited if the range didn't
contain any empty cells.

Biff