help with formula

D

Dr M

I have a spread sheet like this. A1 is a # for Jan 2004, A2 Feb. 2004 down
to dec in A12, Column B is the same but for 2005. row B13 is a YTD for
2005. I need a formula that will automatically count just the same YTD
period of 2004 in A13. My first thought was just average per month and
multiply it by the number of the month we are in but is was not accurate
enough for me due to slow time vs. the busy times. I have many columns to
do so I need something fast. Is it possible to have a cell set up where I
could just input a number (say 4 for April) and then the formula would only
count the first 4 rows, then in May I change it to a 5 and it would count
the first 5 rows and so on?

Thanks in advance to all who answer.
D
 
C

CLR

It's a little round-about, but you could put a list of 1-12 down an unused
column, say column I, and in corresponding cells in collumn J put
=sum(A1:A1), =sum(A1:A2., =sum(A1:A3), etc etc down to 12.

then in A13 put =VLOOKUP(D1,I1:J12,2,FALSE)

then whatever number you put in D1, the formula will sum that many months in
column A..........

Vaya con Dios,
Chuck, CABGx3
 
F

Franz

Dr M said:
I have a spread sheet like this. A1 is a # for Jan 2004, A2 Feb. 2004
down to dec in A12, Column B is the same but for 2005. row B13 is a
YTD for 2005. I need a formula that will automatically count just the
same YTD period of 2004 in A13. My first thought was just average per
month and multiply it by the number of the month we are in but is was
not accurate enough for me due to slow time vs. the busy times. I
have many columns to do so I need something fast. Is it possible to
have a cell set up where I could just input a number (say 4 for
April) and then the formula would only count the first 4 rows, then
in May I change it to a 5 and it would count the first 5 rows and so
on?
Click to expand...

You cantry this formula:

=SUM(OFFSET(A1,,,COUNTA(B1:B12)))

--
Hoping to be helpful...

Regards

Franz
 
Top