spreadsheet formula

[doug]

Good morning,
i have a manager who wants to average a set of cells.
he there are indefinate number of cells in a row and he wants to average
only the current entry plus only the last three consecitive entries.
example:
if a1,b1,c1,d1,f1 all have entries he wants the average based on the info in
b1,c1,d1,f1.
when e1 has data entered he wants the average to be based on c1,d1,f1,e1.
there are 20+ rows that need to have this function.
can someone help, this is beyond my capabilities.
thanks
doug

[Bob Phillips]

Doug,

This will total it for row 1

=SUM(N(OFFSET(A1,,LARGE((1:1<>"")*(COLUMN(INDIRECT("A:IV"))),{1,2,3,4})-1,1,
1)))

it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"doug" <[email protected]> wrote in message
news:[email protected]...
> Good morning,
> i have a manager who wants to average a set of cells.
> he there are indefinate number of cells in a row and he wants to average
> only the current entry plus only the last three consecitive entries.
> example:
> if a1,b1,c1,d1,f1 all have entries he wants the average based on the info
in
> b1,c1,d1,f1.
> when e1 has data entered he wants the average to be based on c1,d1,f1,e1.
> there are 20+ rows that need to have this function.
> can someone help, this is beyond my capabilities.
> thanks
> doug
>

[doug]

Thanks Bob,
i must be doing something wrong though. it works on a blank worksheet but
when i put it into e8 to calulate starting at F8 + it gives me a #N/A error.
i changed the formula to look as follows
=SUM(N(OFFSET(F8,,LARGE((1:1<>"")*(COLUMN(INDIRECT("F:IV"))),{1,2,3,4})-1,1,1))) the formula is in cell E8
thanks
doug


"Bob Phillips" wrote:

> Doug,
>
> This will total it for row 1
>
> =SUM(N(OFFSET(A1,,LARGE((1:1<>"")*(COLUMN(INDIRECT("A:IV"))),{1,2,3,4})-1,1,
> 1)))
>
> it is an array formula, so commit with Ctrl-Shift-Enter, not just Enter.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "doug" <[email protected]> wrote in message
> news:[email protected]...
> > Good morning,
> > i have a manager who wants to average a set of cells.
> > he there are indefinate number of cells in a row and he wants to average
> > only the current entry plus only the last three consecitive entries.
> > example:
> > if a1,b1,c1,d1,f1 all have entries he wants the average based on the info
> in
> > b1,c1,d1,f1.
> > when e1 has data entered he wants the average to be based on c1,d1,f1,e1.
> > there are 20+ rows that need to have this function.
> > can someone help, this is beyond my capabilities.
> > thanks
> > doug
> >
>
>
>

[daddylonglegs]

Try changing to

=AVERAGE(N(OFFSET(A8,,LARGE((F8:IV8<>"")*(COLUMN(INDIRECT("F:IV"))),{1,2,3,4})-1,1,1)))

[doug]

Thanks, that did the trick

"daddylonglegs" wrote:

>
> Try changing to
>
> =AVERAGE(N(OFFSET(A8,,LARGE((F8:IV8<>"")*(COLUMN(INDIRECT("F:IV"))),{1,2,3,4})-1,1,1)))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=500152
>
>