I'm working with a precipitation data base of multiple sheets. Each sheet is a year of measurements.
The thing is that I'm trying to get the average of several municipalities for each year. Is there a way I can automatically replace the 'sheet' in the formula so I don't have to change it manually?
Example: =AVERAGE('1996'!P$9:P$13)
Similar formulas run like this until row 57.
All sheets are numbered according to the year, Can I do a fast copy paste in a way that it replace the sheet name? Ex. '2003', '2006'
I'm assuming that you would like to reference the different sheets from a "Master" sheet.
In the "Master" sheet, place all your sheet names in one cell (e.g. cell X1) using data validation. Make sure that they are spelled exactly the same as the sheet names.
You can then use the INDIRECT function wrapped in the AVERAGE function to reference each sheet as follows:-
=AVERAGE(INDIRECT("'"&$X$1&"'!P:P"))
Select a sheet name from the data validation drop down list in X1 and you'll then notice that the formula will make calculations for that sheet.
The cell reference X1 has to be absolute so make sure that you use the $ symbols as shown in the formula above.
Select the cell in which you wish to place the drop down. In the ribbon at the top of the sheet, select "Data" then, in the "Data Tools" group select "Data Validation".
In the dialogue box that then appears, select "List" from the "Allow" drop down. Ensure that both the "Ignore blank" and the "In-cell drop down" boxes are ticked. Next, in the Source box that appears, type in your sheet names like this:-
Sheet1,Sheet2,Sheet3 (use your actual sheet names here).
with commas as shown but no spaces. Don't place a comma after the last sheet name. Click OK.
Your list of sheet names should now appear in the selected cell.
Most importantly: will the outcome of that wrapped function be an average for EACH year (n= 20) or an average of all years? I'm looking for the first.
As I don't know the set out of your workbook, I can't answer your question. You'll have to try the function and see for yourself if the correct result appears. However, based on the formula that you supplied, as far as I can tell, it will average whatever numerical data you have in your selected range (Column P) for the selected year sheet.
6897 users have said thank you to us this month
- Posts
- 3
- Registration date
- Friday July 12, 2019
- Status
- Member
- Last seen
- July 13, 2019
-When I place all the sheet names in one cell, should I place comas? should I enter them just with a space in between?
Most importantly: will the outcome of that wrapped function be an average for EACH year (n= 20) or an average of all years? I'm looking for the first.
Thanks!!!!!