help with formula

  • Thread starter Thread starter Dr M
  • Start date Start date
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
 
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 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
 
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
 
Back
Top