Request for formula
 

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Is the negative width an undocumented feature of the offset formula?

I modified Mike's formula slightly:

=SUM(OFFSET(B2,,COUNT(B2:Z2)-4,,4))

Change Z to whatever is your rightmost month column.

Lars-Åke

On Sun, 1 Feb 2009 00:40:00 -0800, Mike H
wrote:

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Hi,


What advantage do you see in the change you made to my formula?


Mike

"Lars-Ã…ke Aspelin" wrote:


Is the negative width an undocumented feature of the offset formula?

I modified Mike's formula slightly:

=SUM(OFFSET(B2,,COUNT(B2:Z2)-4,,4))

Change Z to whatever is your rightmost month column.

Lars-Ã…ke

On Sun, 1 Feb 2009 00:40:00 -0800, Mike H
wrote:

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

bit long one

=SUM(INDIRECT("r"&ROW(2:2)&"c"&COUNT(A2:P2)-3&":r"&ROW(2:2)&"c"&COUNT
(A2:P2),0))

On Feb 1, 1:13*pm, Eddie wrote:
* * *A * * * * * B * * *C * * * D * * * E * * * F *......... *AA
* * *Name * *Jan * *Feb * Mar * *Apr * May........ *
1 * *a * * * * 10 * * 9 * * *22 * * * 15 * * *15
2 * *b * * * * 12 * * 3 * * *11 * * * 18 * * *12
3 * *c * * * * 12 * * 11 * * 16 * * * 17 * * 23
4 * *d * * * * 23 * * 22 * * 12 * * * 21 * * 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

I read in the help for OFFSET function that width must be a positive
number, see
http://office.microsoft.com/en-us/ex...092081033.aspx
http://office.microsoft.com/en-us/ex...624151033.aspx
but sometimes there are some undocumented features that can be useful,
so that's why I asked you about the negative width.

After posting I found this page where it is stated that a negative
width could give problems in earlier versions of Excel, see
http://support.microsoft.com/kb/184109
So, the advantage is to avoid this (possible) problem.

The change from B2 to A2 is just to be able to handle possible numeric
names.

Lars-Åke

On Sun, 1 Feb 2009 01:14:00 -0800, Mike H
wrote:

Hi,


What advantage do you see in the change you made to my formula?


Mike

"Lars-Åke Aspelin" wrote:


Is the negative width an undocumented feature of the offset formula?

I modified Mike's formula slightly:

=SUM(OFFSET(B2,,COUNT(B2:Z2)-4,,4))

Change Z to whatever is your rightmost month column.

Lars-Åke

On Sun, 1 Feb 2009 00:40:00 -0800, Mike H
wrote:

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Hi,

That's interesting that using negative width giving problems, I always used
it that way because I think it's quite intuative when you look at the formula
and I've never experienced problems. Thanks for the links.

Mike

"Lars-Ã…ke Aspelin" wrote:

I read in the help for OFFSET function that width must be a positive
number, see
http://office.microsoft.com/en-us/ex...092081033.aspx
http://office.microsoft.com/en-us/ex...624151033.aspx
but sometimes there are some undocumented features that can be useful,
so that's why I asked you about the negative width.

After posting I found this page where it is stated that a negative
width could give problems in earlier versions of Excel, see
http://support.microsoft.com/kb/184109
So, the advantage is to avoid this (possible) problem.

The change from B2 to A2 is just to be able to handle possible numeric
names.

Lars-Ã…ke

On Sun, 1 Feb 2009 01:14:00 -0800, Mike H
wrote:

Hi,


What advantage do you see in the change you made to my formula?


Mike

"Lars-Ã…ke Aspelin" wrote:


Is the negative width an undocumented feature of the offset formula?

I modified Mike's formula slightly:

=SUM(OFFSET(B2,,COUNT(B2:Z2)-4,,4))

Change Z to whatever is your rightmost month column.

Lars-Ã…ke

On Sun, 1 Feb 2009 00:40:00 -0800, Mike H
wrote:

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Meant to add that because of this potential error I'll use it the way you
suggest in future.

Thanks again

"Mike H" wrote:

Hi,

That's interesting that using negative width giving problems, I always used
it that way because I think it's quite intuative when you look at the formula
and I've never experienced problems. Thanks for the links.

Mike

"Lars-Ã…ke Aspelin" wrote:

I read in the help for OFFSET function that width must be a positive
number, see
http://office.microsoft.com/en-us/ex...092081033.aspx
http://office.microsoft.com/en-us/ex...624151033.aspx
but sometimes there are some undocumented features that can be useful,
so that's why I asked you about the negative width.

After posting I found this page where it is stated that a negative
width could give problems in earlier versions of Excel, see
http://support.microsoft.com/kb/184109
So, the advantage is to avoid this (possible) problem.

The change from B2 to A2 is just to be able to handle possible numeric
names.

Lars-Ã…ke

On Sun, 1 Feb 2009 01:14:00 -0800, Mike H
wrote:

Hi,


What advantage do you see in the change you made to my formula?


Mike

"Lars-Ã…ke Aspelin" wrote:


Is the negative width an undocumented feature of the offset formula?

I modified Mike's formula slightly:

=SUM(OFFSET(B2,,COUNT(B2:Z2)-4,,4))

Change Z to whatever is your rightmost month column.

Lars-Ã…ke

On Sun, 1 Feb 2009 00:40:00 -0800, Mike H
wrote:

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Hello Eddie,

I would not use any volatile function like INDIRECT or OFFSET but
(enter into AA2 and copy down):
=SUM(INDEX(B2:Z2,1,MAX(1,MATCH($AA$1,$B$1:$Z$1,1)-3)):INDEX
(B2:Z2,1,MATCH($AA$1,$B$1:$Z$1,1)))

You have to prepare your worksheet like this:
Enter into B1: 1/1/2009 and format the cell with the custom format
mmm. Then you would see "Jan". Do this for C1, D1, ... accordingly.

Then you can flexibly enter into AA1: 1/7/2009 to sum Apr, May, Jun,
Jul or enter 1/4/2009 to add Jan - Apr. If you enter 1/2/2009 it would
sum up only Jan - Feb it this formula would not fail - this might be
what you want if you don't have 4 subsequent months yet.

Regards,
Bernd

Request for formula
 

Put this formula in AA2 and copy it down....

=SUMPRODUCT((COLUMN(B2:Z2)SUMPRODUCT(MAX((B2:Z2< "")*COLUMN(B2:Z2)))-4)*B2:Z2)

--
Rick (MVP - Excel)


"Eddie" wrote in message ...
A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you

Request for formula
 

Hi Rick,

Just a quick question

why the -1? why not start at B2?

=SUM(OFFSET(A2,0,COUNT(B2:M2),,-4))


Cheers,
Shane Devenshire


"Mike H" wrote:

Hi,

Try

=SUM(OFFSET(A2,,COUNT(A2:P2)-1,,-4))

Mike

"Eddie" wrote:

A B C D E F ......... AA
Name Jan Feb Mar Apr May........
1 a 10 9 22 15 15
2 b 12 3 11 18 12
3 c 12 11 16 17 23
4 d 23 22 12 21 22

I am using office 2007,I will enter the figure every month into this
worksheet. what formula should I use to sum the latest 4 months of figure and
show it in column AA?

Thank you