help with formula

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

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



"Dr M" <[email protected]> wrote in message
news:tTzee.1236739$8l.481184@pd7tw1no...
> 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
>
>

How about this, with that month number in A20

=SUM(OFFSET(A1,,,A20))

--
HTH

Bob Phillips

"Dr M" <[email protected]> wrote in message
news:tTzee.1236739$8l.481184@pd7tw1no...
> 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
>
>

"Dr M" <[email protected]>ha scritto nel messaggio tTzee.1236739$8l.481184@pd7tw1no

> 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?

You cantry this formula:

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

--
Hoping to be helpful...

Regards

Franz

----------------------------------------------------------------------------------------
To reply translate from italian InVento (no capital letters)
----------------------------------------------------------------------------------------

I hope I have an understanding of what you are trying to do.

You have monthly sales figures (for example) in their respective rows and
the years in the columns. You want to get the current year -to-date info or
an accumulation of data for a specific period and compare one year to the
next.

How am I doing so far?

Somewhere in the spreadsheet, either displayed or not, say cell (a20), enter
=month(Today()) or if input is required point the formula above to the cell
that has the month you want to sum data to. i.e. in cell a21 enter the
month you want to sum to.

I am assuming that column a contains a date. In column b enter =month(a1)
Then copy that formula down for each month of the year. Then in the cells
you want the comparative data, enter the array formula
{=SUM(IF(B2:B13<=A20,A2:A13))}. Do this for the totals you want for each
column. as you progress thru the year your comparative monthly numbers will
be calculated as you enter data and change the month number you desire.

Hope this helps.

Jerry


"Dr M" <[email protected]> wrote in message
news:tTzee.1236739$8l.481184@pd7tw1no...
>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
>