Request for formula

  • Thread starter Thread starter Eddie
  • Start date Start date
E

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
 
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
 
bit long one

=SUM(INDIRECT("r"&ROW(2:2)&"c"&COUNT(A2:P2)-3&":r"&ROW(2:2)&"c"&COUNT
(A2:P2),0))
 
I read in the help for OFFSET function that width must be a positive
number, see
http://office.microsoft.com/en-us/excel/HP052092081033.aspx
http://office.microsoft.com/en-us/excel/HP100624151033.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
 
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
 
Meant to add that because of this potential error I'll use it the way you
suggest in future.

Thanks again
 
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
 
Put this formula in AA2 and copy it down....

=SUMPRODUCT((COLUMN(B2:Z2)>SUMPRODUCT(MAX((B2:Z2<>"")*COLUMN(B2:Z2)))-4)*B2:Z2)
 
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
 
Back
Top