New formula needed, gives avg numbers based on specific fields

usnapoleon

New Member
Joined
May 22, 2014
Messages
46
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
ABCDEFGHI
1Monthly Income Audit Budget Input sheet
219DayMonth1234
312MonthTotal Days31 29 31 30
431TT Days
5JanuaryFebruaryMarchApril
6Avg DayMTDYTDRENTAL BUDGETED REVENUE
7A1
82865434101244Total Rooms 8,866 8,294 8,866 8,580
9000House 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.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

AliGW

.
Joined
Mar 9, 2014
Messages
3,628
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

New Member
Joined
May 22, 2014
Messages
46
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
May 22, 2014
Messages
46
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top