Dynamic Year-To-Date Formula
 

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:

Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400

I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.


Thanks

One way

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9, 11,13,15,17,19,21,23))),2)=1),OFFSET($A$2,,,,CHOOS E($A$10,1,3,5,7,9,11,13,15,17,19,21,23)))

where A2 holds Jan actuals

Of course if you will only need to sum up to the last entered actual and
there is no more actuals entered you can use

=SUMPRODUCT(--(MOD(COLUMN(A2:X2),2)=1),A2:X2)

In the first formula the cell where you would put the months number is A10,
so replace that cell with the actual cell, you might also want to guard
against if that cell is empty (will return an error)

=IF($A$10="","",SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9, 11,13,15,17,19,21,23))),2)=1),OFFSET($A$2,,,,CHOOS E($A$10,1,3,5,7,9,11,13,15,17,19,21,23))))


Regards,

Peo Sjoblom



"snax500" wrote:

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:

Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400

I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.


Thanks

I've used the HLookup for this.

To do this I'd have =today() in your cell A1 formated to display only the
month. Then I'd custom format the "actual" column headders to display MMM
"Actual" based on the number in that cell (then the number 2 would read Feb
Actual). With the HLookup I'd check cell A1 (the =today() formula) and
bounce it aginst the numbers in the "Actual" column headders and return the
cell below that.

For me this made it dynamic enough to display upon opening the current
actual value w/o input. If you wanted to look at prior months, cell A1 would
need adjust ment, for that I use a secondary input cell so my forumula reads
=today()-Z1. Then I can use Z1 to add or subtract from the date w/o loosing
my reference cell.

Good luck.

"snax500" wrote:

In Excel2000, I would like to create a dynamic year-to-date formula. I
have the following data starting in row 3 and going from column A to
column X:

Jan Actuals Jan Budget Feb Actuals Feb Budget
100 200 300 400

I would like to use cell A1 as an input cell for the month ( ex. Feb =
2). I would like the formula to lookup the month and then tell me how
much Feb YTD Actuals are ( in this example = 400 ). All I want to
change each month is the input cell in A1 and have the formula
automatically give me the results.


Thanks

Thanks,

It works perfectly. Can you please tell me how this formula works. I am
not that familiar with these functions.

Thanks again.

=SUMPRODUCT(--(MOD(COLUMN(OFFSET($A$2,,,,CHOOSE($A$10,1,3,5,7,9, 11,13,15,17,19,21,23))),2)=1),OFFSET($A$2,,,,CHOOS E($A$10,1,3,5,7,9,11,13,15,17,19,21,23)))