Home |
Search |
Today's Posts |
#1
|
|||
|
|||
YTD Budget Sum if Actual Month has activities
My worksheet has 50 rows showing sales for each book. It has 39 columns - 3
for each month - budget, actual and diff - and 3 more columns for YTD. They are like this: Jan-Budget Jan-Actual Jan-Diff Feb-Budget Feb-Actual Feb-Diff etc. I have budgets for each month for each book on the worksheet already. I update it monthly to enter actual sales for the month. I want the YTD-Budget column to only sum up months that have actual activities so I can do a fair comparison with YTD-Actual. I tried to create a formula that sums up the budget columns for months where Total Actual for the month is < 0. But I didnt' get it right. I'd appreciate if you can help me with that. Thanks in advance! |
#2
|
|||
|
|||
Let's assume...
Your data is in columns A thru AM [the first 39 columns in a worksheet]. Your YTD Budget is in Column AK. Your data begins on row 2. ---------------------------------- (1) In Columns AO thru AZ, create YTD Budget columns. i.e.: AO is YTD Jan thru Jan, AP is YTD JAn thru Feb...AZ is Jan thru Dec (2) On a monthly basis, put the Column letter in AN1. i.e.: In Jan enter AO in cell AN1, In Feb enter AP in cell AN1, In Dec enter AZ in AN1. (3) In your YTD Budget column - [cell AK2], put the following formula... =INDIRECT($AN$1&ROW()) Copy this down your 50 rows. ---------------------------------- What the formula does... It looks at the column you've indicated in cell 'AN1' (case insensitive). It then looks at the row that the formula is on and puts the 2 pieces of information together. The Indirect function then looks at that information which looks like a cell address to it and grabs the value that's in that cell address. i.e.: If you put AW in cell AN1 and your formula is on row 17, the indirect function will return the value in cell AW17. HTH, Gary Brown "AGnes" wrote: My worksheet has 50 rows showing sales for each book. It has 39 columns - 3 for each month - budget, actual and diff - and 3 more columns for YTD. They are like this: Jan-Budget Jan-Actual Jan-Diff Feb-Budget Feb-Actual Feb-Diff etc. I have budgets for each month for each book on the worksheet already. I update it monthly to enter actual sales for the month. I want the YTD-Budget column to only sum up months that have actual activities so I can do a fair comparison with YTD-Actual. I tried to create a formula that sums up the budget columns for months where Total Actual for the month is < 0. But I didnt' get it right. I'd appreciate if you can help me with that. Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a Yearly, by month, budget in Excel? | Excel Discussion (Misc queries) | |||
Month Year Date Format | Excel Worksheet Functions | |||
Automatically change tab reference | Excel Discussion (Misc queries) | |||
Find and Replace | Excel Discussion (Misc queries) | |||
How to extract month number from month name | Excel Discussion (Misc queries) |