New formula needed, gives avg numbers based on specific fields

[usnapoleon]

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

	A	B	C	D	E	F	G	H	I
Monthly Income Audit Budget Input sheet
Day
Month
TT Days
January	February	March	April
Avg Day	MTD	YTD
House Use/Model

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: right"]Month[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #969696, align: right"]Total Days[/TD]
[TD="bgcolor: #CCFFFF, align: center"]31 [/TD]
[TD="bgcolor: #CCFFFF, align: center"]29 [/TD]
[TD="bgcolor: #CCFFFF, align: center"]31 [/TD]
[TD="bgcolor: #CCFFFF, align: center"]30 [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #000000"]RENTAL BUDGETED REVENUE[/TD]
[TD="bgcolor: #000000, align: right"][/TD]
[TD="bgcolor: #000000, align: right"][/TD]
[TD="bgcolor: #000000, align: right"][/TD]
[TD="bgcolor: #000000, align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FF0000, align: center"]A1[/TD]
[TD="bgcolor: #FF0000, align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"][/TD]
[TD="bgcolor: #FF0000, align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #D9D9D9, align: right"]286[/TD]
[TD="bgcolor: #D9D9D9, align: right"]5434[/TD]
[TD="bgcolor: #D9D9D9, align: right"]101244[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #D9D9D9"]Total Rooms[/TD]
[TD="bgcolor: #D9D9D9, align: right"] 8,866 [/TD]
[TD="bgcolor: #D9D9D9, align: right"] 8,294 [/TD]
[TD="bgcolor: #D9D9D9, align: right"] 8,866 [/TD]
[TD="bgcolor: #D9D9D9, align: right"] 8,580 [/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="bgcolor: #D9D9D9, align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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.

 

[AliGW]

So instead of 1 in the formula, use this:

ROW(A1)

This will increment as you drag downwards, from 1 to 2 to 3, etc.

 

[usnapoleon]

It did drag it down properly!! Can you explain something for me, because I like to understand the concepts as well. So when it was simply '1' in my formula, it's looking at the data in row 1 of my array, which begins in row 8 of the spreadsheet as in the visual above. When I change it like you suggested, the calculated data didnt change (which is good) but I'm trying to figure out how and why it didnt change. Because instead of looking at row 1 of the array (which is row 8 of the spreadsheet), im literally telling it to look at A1. So how does it still calculate correctly?

 

[FDibbins]

As a way to explain this, just type =ROW(A1) into a cell (any cell, anywhere) - it will show 1 for row 1
then copy down
As you copy it down, it will increment as you copy it down, because it progresses as follows...
=ROW(A1)...shows 1
=ROW(A2)...shows 2
=ROW(A3)...shows 3
=ROW(A4)...shows 4
etc

applying that to your formula makes it look at 1, then 2 then 3 etc. By comparison, just hard-coding 1, keeps 1 there all the time

Hope that explained it?

Shout if you need more help

 

[usnapoleon]

Ahhhh!!!! Neat, thanks! I got it!!! Much appreciated to you both for your help, this will save my rear when I need to make changes on this sheet!! (Which I need to do but hesitated due to the trouble it caused)

 

[FDibbins]

Actually, possibly a better option would be to use =ROWS($1:1) rather than just =ROWA1)

=ROWS($1:1) is more robust and is not affected if you need to insert rows at row 1

=ROW(A1) becomes =ROW(A2) if you insert a row at row 1, but =ROWS($1:1) stays just like that.

A useful tool to know if you need to have some part of a formula that needs to increment