[Eddie]

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

[Mike H]

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

[Lars-Åke Aspelin[_2_]]

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

[Mike H]

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

[muddan madhu]

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

[Lars-Åke Aspelin[_2_]]

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

[Mike H]

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

[Mike H]

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

[Bernd P]

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

[Rick Rothstein]

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

[Shane Devenshire[_2_]]

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