Rolling Average

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

Hi!

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

Biff

"Bearcats_85" <[email protected]> wrote in message
news:[email protected]...
>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

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

"Biff" wrote:

> Hi!
>
> Is the range going down a column or across a row?
>
> Biff
>
> "Bearcats_85" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

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!
==============================================



"Bearcats_85" <[email protected]> wrote in message
news:[email protected]...
> I 12 rows of data, each row requires the rolling average.
>
> "Biff" wrote:
>
> > Hi!
> >
> > Is the range going down a column or across a row?
> >
> > Biff
> >
> > "Bearcats_85" <[email protected]> wrote in message
> > news:[email protected]...
> > >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
> >
> >
> >

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

"Bearcats_85" <[email protected]> wrote in message
news:[email protected]...
>I 12 rows of data, each row requires the rolling average.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Is the range going down a column or across a row?
>>
>> Biff
>>
>> "Bearcats_85" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>>
>>
>>

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

"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> 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!
> ==============================================
>
>
>
> "Bearcats_85" <[email protected]> wrote in message
> news:[email protected]...
>> I 12 rows of data, each row requires the rolling average.
>>
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > Is the range going down a column or across a row?
>> >
>> > Biff
>> >
>> > "Bearcats_85" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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
>> >
>> >
>> >
>

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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "RagDyer" <[email protected]> wrote in message
> news:[email protected]...
>> 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!
>> ==============================================
>>
>>
>>
>> "Bearcats_85" <[email protected]> wrote in message
>> news:[email protected]...
>>> I 12 rows of data, each row requires the rolling average.
>>>
>>> "Biff" wrote:
>>>
>>> > Hi!
>>> >
>>> > Is the range going down a column or across a row?
>>> >
>>> > Biff
>>> >
>>> > "Bearcats_85" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> > >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
>>> >
>>> >
>>> >
>>
>
>

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 !
--------------------------------------------------------------------

"Biff" <[email protected]> wrote in message
news:[email protected]...
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

"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> 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!
> ==============================================
>
>
>
> "Bearcats_85" <[email protected]> wrote in message
> news:[email protected]...
>> I 12 rows of data, each row requires the rolling average.
>>
>> "Biff" wrote:
>>
>> > Hi!
>> >
>> > Is the range going down a column or across a row?
>> >
>> > Biff
>> >
>> > "Bearcats_85" <[email protected]> wrote in message
>> > news:[email protected]...
>> > >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
>> >
>> >
>> >
>