Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional SUM for Budget, Actuals, & Variance YTD columns
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
chart with actuals and limits | Charts and Charting in Excel | |||
to compare sales plan vs actuals from two different worksheets? | Excel Worksheet Functions | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
Divide Monthly Sales Budget to Day Budget | Excel Worksheet Functions | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) |