G
Guest
I am sure that this has been asked/answered by thousands of
accountants across the world, but I am still struggling to get a
formula that works and would definitely appreciate your help. I tried
asking this question in another group but did not get a formula that
worked (I think because of the Variance column). So, I am also trying
here.
I need a formula that calculates Year-To-Date figures automatically
(based on TODAY() or optionally on a user-entered planning date),
without editing the existing formulas each month. My table is divided
in months (Jan, Feb, Mar...).
Each month has three columns: 1) budget, 2) Actuals, and 3)Variance.
And there are an additional 3 columns for YTD totals.
I need a formula that can calculate automatically year to date (YTD)
figures. The budget amounts vary from month the month (and obviously
the actuals will also).
Table looks like
Row 1: Header info
Row 2: Header info (except C2 has an optional user entered date that
will be used to grab the month for planning/what if scenarios)
Row 3: Header info (basically dates to show Jan-07, Feb-07, Mar-07,
etc.)
Row 4: Header info
Row 5: The start of the actual data
Row nnn: data (but I think if I get the formula right for one row that
it will work for all rows.
Column A: Grouping labels
Column B: Sub-grouping labels
Column C: Account description
Column D: YTD Budget totals
Column E: YTD Actual totals
Column F: YTD Variance amounts
Column G: Jan Budget amounts
Column H: Jan Actual amounts
Column I: Jan Variance amounts
Column J: Feb Budget amounts
....
Column AP: Dec Variance amounts
The trick is that I need a conditional SUM function.
I initially tried to use a series of nested IFs; however, there are
too many months and the nesting went too deep.
What I am trying to do for the YTD columns is...
If there is NOT a manually-entered AsOf date (in C2), then I would
like the YTD columns to compare TODAY() to the month indicated in the
headings of row 3 and only take values for =SUM for months that are
complete. For example, if today is 2/15, then I would like the sum
function in the YTD columns to only include the amounts from the
January columns. But if today was 12/15, then then I would like the
sum function in the YTD columns to include the amounts from the
January - November columns. Again, the sum has to skip columns to
pick up every third value so that monthly Budget columns are summed in
the YTD Budget column, monthly Actuals columns are summed in the YTD
Actuals column, and monthly Variance columns are summed in the YTD
Variance column.
Similarly, if there IS a manually entered AsOf date (in C2), then I
would like the YTD columns to use that date rather than TODAY() for
the comparison to determine what to include in the SUM (summing all
months up to but not including the month specified in the user-
supplied date). For example, if the user types in 8/15 then I would
like the sum function in the YTD columns to include the amounts from
the January - July columns (but not August).
Surely, this is a popular requirement among accountants?? There just
has to be an easy way to do this. I am just not sure what it is? Can
you help me?
accountants across the world, but I am still struggling to get a
formula that works and would definitely appreciate your help. I tried
asking this question in another group but did not get a formula that
worked (I think because of the Variance column). So, I am also trying
here.
I need a formula that calculates Year-To-Date figures automatically
(based on TODAY() or optionally on a user-entered planning date),
without editing the existing formulas each month. My table is divided
in months (Jan, Feb, Mar...).
Each month has three columns: 1) budget, 2) Actuals, and 3)Variance.
And there are an additional 3 columns for YTD totals.
I need a formula that can calculate automatically year to date (YTD)
figures. The budget amounts vary from month the month (and obviously
the actuals will also).
Table looks like
Row 1: Header info
Row 2: Header info (except C2 has an optional user entered date that
will be used to grab the month for planning/what if scenarios)
Row 3: Header info (basically dates to show Jan-07, Feb-07, Mar-07,
etc.)
Row 4: Header info
Row 5: The start of the actual data
Row nnn: data (but I think if I get the formula right for one row that
it will work for all rows.
Column A: Grouping labels
Column B: Sub-grouping labels
Column C: Account description
Column D: YTD Budget totals
Column E: YTD Actual totals
Column F: YTD Variance amounts
Column G: Jan Budget amounts
Column H: Jan Actual amounts
Column I: Jan Variance amounts
Column J: Feb Budget amounts
....
Column AP: Dec Variance amounts
The trick is that I need a conditional SUM function.
I initially tried to use a series of nested IFs; however, there are
too many months and the nesting went too deep.
What I am trying to do for the YTD columns is...
If there is NOT a manually-entered AsOf date (in C2), then I would
like the YTD columns to compare TODAY() to the month indicated in the
headings of row 3 and only take values for =SUM for months that are
complete. For example, if today is 2/15, then I would like the sum
function in the YTD columns to only include the amounts from the
January columns. But if today was 12/15, then then I would like the
sum function in the YTD columns to include the amounts from the
January - November columns. Again, the sum has to skip columns to
pick up every third value so that monthly Budget columns are summed in
the YTD Budget column, monthly Actuals columns are summed in the YTD
Actuals column, and monthly Variance columns are summed in the YTD
Variance column.
Similarly, if there IS a manually entered AsOf date (in C2), then I
would like the YTD columns to use that date rather than TODAY() for
the comparison to determine what to include in the SUM (summing all
months up to but not including the month specified in the user-
supplied date). For example, if the user types in 8/15 then I would
like the sum function in the YTD columns to include the amounts from
the January - July columns (but not August).
Surely, this is a popular requirement among accountants?? There just
has to be an easy way to do this. I am just not sure what it is? Can
you help me?