usnapoleon
Board Regular
- Joined
- May 22, 2014
- Messages
- 82
- Office Version
- 365
- Platform
- Windows
Hello
I have a spreadsheet with 12 columns, one for each month. Given the month I specify (using a number 1-12), I have 3 columns that calculate an avg per day, a mtd, and a ytd total. See the image below
Excel 2010
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
This is the formula I have for the avg day in cell A8:
=(INDEX(F$8:Q$530,1,MATCH(A$3,F$2:Q$2,0)))/A$4
Right now, because I have month 12 above in cell A3, it's looking at December, in column Q (sorry I didnt copy/paste my spreadsheet that far)
It works, but when I want to add rows or delete rows, the formula does not update itself when you spread it downward. Nothing changes on it, and the number I need to change is the one before the Match part... in the formula above, it's the '1'. So when I spread it down, i'd like it to autochange to 2, then 3, etc. My hope in this thread is to devise a new formula that does the same thing but spreads better. I found it to be a real pain to add or remove rows, and other tabs in my spreadsheet that pull from this information wouldnt auto-update to the new rows whenever I added or removed rows on this tab, and I'm pretty certain it's due to this.
I have a spreadsheet with 12 columns, one for each month. Given the month I specify (using a number 1-12), I have 3 columns that calculate an avg per day, a mtd, and a ytd total. See the image below
Excel 2010
A | B | C | D | E | F | G | H | I | |
---|---|---|---|---|---|---|---|---|---|
1 | Monthly Income Audit Budget Input sheet | ||||||||
2 | 19 | Day | Month | 1 | 2 | 3 | 4 | ||
3 | 12 | Month | Total Days | 31 | 29 | 31 | 30 | ||
4 | 31 | TT Days | |||||||
5 | January | February | March | April | |||||
6 | Avg Day | MTD | YTD | RENTAL BUDGETED REVENUE | |||||
7 | A1 | ||||||||
8 | 286 | 5434 | 101244 | Total Rooms | 8,866 | 8,294 | 8,866 | 8,580 | |
9 | 0 | 0 | 0 | House Use/Model |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Budget
This is the formula I have for the avg day in cell A8:
=(INDEX(F$8:Q$530,1,MATCH(A$3,F$2:Q$2,0)))/A$4
Right now, because I have month 12 above in cell A3, it's looking at December, in column Q (sorry I didnt copy/paste my spreadsheet that far)
It works, but when I want to add rows or delete rows, the formula does not update itself when you spread it downward. Nothing changes on it, and the number I need to change is the one before the Match part... in the formula above, it's the '1'. So when I spread it down, i'd like it to autochange to 2, then 3, etc. My hope in this thread is to devise a new formula that does the same thing but spreads better. I found it to be a real pain to add or remove rows, and other tabs in my spreadsheet that pull from this information wouldnt auto-update to the new rows whenever I added or removed rows on this tab, and I'm pretty certain it's due to this.