I have A1 to L1 total 12 cells reserved to enter the monthly values. m1 will
have last 3 months =Average(a1:c1). I need avoid the shifting of reference
cells every month in the formula to calculate last 3 month Average. Any
alternative formula I can apply to auto calculate the Average amount based on
3 value filled cells from the left end of the row? I mean if I fill apr value
in D1, then same result from =Average (b1: d1) which should automatically
return in m1.
Thanks

To average the last three values in row #1 use:

=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1 )),3)))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

--
Gary''s Student - gsnu200813


"Narnimar" wrote:

I have A1 to L1 total 12 cells reserved to enter the monthly values. m1 will
have last 3 months =Average(a1:c1). I need avoid the shifting of reference
cells every month in the formula to calculate last 3 month Average. Any
alternative formula I can apply to auto calculate the Average amount based on
3 value filled cells from the left end of the row? I mean if I fill apr value
in D1, then same result from =Average (b1: d1) which should automatically
return in m1.
Thanks

So I put a1 to e1 with 25 28 32 14 15 and placed this formula in m1. Its
returns 0. Any reason?


"Gary''s Student" wrote:

To average the last three values in row #1 use:

=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1 )),3)))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

--
Gary''s Student - gsnu200813


"Narnimar" wrote:

I have A1 to L1 total 12 cells reserved to enter the monthly values. m1 will
have last 3 months =Average(a1:c1). I need avoid the shifting of reference
cells every month in the formula to calculate last 3 month Average. Any
alternative formula I can apply to auto calculate the Average amount based on
3 value filled cells from the left end of the row? I mean if I fill apr value
in D1, then same result from =Average (b1: d1) which should automatically
return in m1.
Thanks

My problem... We need a small change because M1 is in the same row as the
data we are trying to average:

=AVERAGE(L1:INDEX(A1:L1,LARGE(IF(A1:L1<"",COLUMN( A1:L1)),3)))

CNTRL-SHFT-ENTER still applies!!
--
Gary''s Student - gsnu200813


"Narnimar" wrote:

So I put a1 to e1 with 25 28 32 14 15 and placed this formula in m1. Its
returns 0. Any reason?


"Gary''s Student" wrote:

To average the last three values in row #1 use:

=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1 )),3)))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

--
Gary''s Student - gsnu200813


"Narnimar" wrote:

I have A1 to L1 total 12 cells reserved to enter the monthly values. m1 will
have last 3 months =Average(a1:c1). I need avoid the shifting of reference
cells every month in the formula to calculate last 3 month Average. Any
alternative formula I can apply to auto calculate the Average amount based on
3 value filled cells from the left end of the row? I mean if I fill apr value
in D1, then same result from =Average (b1: d1) which should automatically
return in m1.
Thanks

Similarly how can I do the same if those values are in merged cells?

"Gary''s Student" wrote:

My problem... We need a small change because M1 is in the same row as the
data we are trying to average:

=AVERAGE(L1:INDEX(A1:L1,LARGE(IF(A1:L1<"",COLUMN( A1:L1)),3)))

CNTRL-SHFT-ENTER still applies!!
--
Gary''s Student - gsnu200813


"Narnimar" wrote:

So I put a1 to e1 with 25 28 32 14 15 and placed this formula in m1. Its
returns 0. Any reason?


"Gary''s Student" wrote:

To average the last three values in row #1 use:

=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1 )),3)))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

--
Gary''s Student - gsnu200813


"Narnimar" wrote:

I have A1 to L1 total 12 cells reserved to enter the monthly values. m1 will
have last 3 months =Average(a1:c1). I need avoid the shifting of reference
cells every month in the formula to calculate last 3 month Average. Any
alternative formula I can apply to auto calculate the Average amount based on
3 value filled cells from the left end of the row? I mean if I fill apr value
in D1, then same result from =Average (b1: d1) which should automatically
return in m1.
Thanks

I have not been able to get this type of formula to work on merged cells.
--
Gary''s Student - gsnu200813


"Narnimar" wrote:

Similarly how can I do the same if those values are in merged cells?

"Gary''s Student" wrote:

My problem... We need a small change because M1 is in the same row as the
data we are trying to average:

=AVERAGE(L1:INDEX(A1:L1,LARGE(IF(A1:L1<"",COLUMN( A1:L1)),3)))

CNTRL-SHFT-ENTER still applies!!
--
Gary''s Student - gsnu200813


"Narnimar" wrote:

So I put a1 to e1 with 25 28 32 14 15 and placed this formula in m1. Its
returns 0. Any reason?


"Gary''s Student" wrote:

To average the last three values in row #1 use:

=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1<"",COLUMN(1:1 )),3)))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

--
Gary''s Student - gsnu200813


"Narnimar" wrote:

I have A1 to L1 total 12 cells reserved to enter the monthly values. m1 will
have last 3 months =Average(a1:c1). I need avoid the shifting of reference
cells every month in the formula to calculate last 3 month Average. Any
alternative formula I can apply to auto calculate the Average amount based on
3 value filled cells from the left end of the row? I mean if I fill apr value
in D1, then same result from =Average (b1: d1) which should automatically
return in m1.
Thanks