Blind Spot in my Excel Formula Abilities
December 23, 2009 6:32 AM Subscribe
Excel Idiot Filter: Is there some simpler way to calculate the averages of the same cell along multiple worksheets?
For example, say I'd like to take the total box on every previous worksheet and average it on a final worksheet. It's the same box on every single one, but I can't seem to get it to work. I've tried letting Excel write the formula for me, but after all that clicking it says the formula doesn't work. Can the worksheets be ranged? For example Say I have =AVERAGE('Sep 09 Wk 2'!H20,'Sep 09 Wk 3'!H20,'Sep 09 Wk 4'!H20). Can I write =AVERAGE('Sep 09 Wk 2':'Sep 09 Wk 4'!H20)? Also is there a limit on the number of things you can have in the formula, which could be causing Excel to not understand it, even when I try writing it using clicks?
For example, say I'd like to take the total box on every previous worksheet and average it on a final worksheet. It's the same box on every single one, but I can't seem to get it to work. I've tried letting Excel write the formula for me, but after all that clicking it says the formula doesn't work. Can the worksheets be ranged? For example Say I have =AVERAGE('Sep 09 Wk 2'!H20,'Sep 09 Wk 3'!H20,'Sep 09 Wk 4'!H20). Can I write =AVERAGE('Sep 09 Wk 2':'Sep 09 Wk 4'!H20)? Also is there a limit on the number of things you can have in the formula, which could be causing Excel to not understand it, even when I try writing it using clicks?
Response by poster: Nope, no dice. It doesn't work.
posted by itsonreserve at 7:13 AM on December 23, 2009
posted by itsonreserve at 7:13 AM on December 23, 2009
Response by poster: And I'm trying to put 32 arguments in my function. Thank you odinsdream for the helpful resource. trying just 30 arguments works perfectly.
posted by itsonreserve at 7:23 AM on December 23, 2009
posted by itsonreserve at 7:23 AM on December 23, 2009
I did a little research on this and what i found shows that if you have sheets at the beginning and end of the sheets in questions, named start and end for example, you could then in the summary sheet say =AVERAGE('start:end'!H20) and it will do an average of all sheets between those two, regardless of how they're named.
This worked for me in Excel 2003 and Excel 2010.
But as shown above, Excel shouldn't need to know that Wk2 is 2 weeks behind Wk4, it just needs to do an average of all the cells on sheets that are positioned between those two sheets. Maybe the error is with the quotes? Try this:
=AVERAGE('Sep 09 Wk 2:Sep 09 Wk 4'!H20)
Put quotes around the entire range, not around the individual names. I was able to break the formula by putting quotes around the individual names.
posted by bDiddy at 7:54 AM on December 23, 2009 [1 favorite]
This worked for me in Excel 2003 and Excel 2010.
But as shown above, Excel shouldn't need to know that Wk2 is 2 weeks behind Wk4, it just needs to do an average of all the cells on sheets that are positioned between those two sheets. Maybe the error is with the quotes? Try this:
=AVERAGE('Sep 09 Wk 2:Sep 09 Wk 4'!H20)
Put quotes around the entire range, not around the individual names. I was able to break the formula by putting quotes around the individual names.
posted by bDiddy at 7:54 AM on December 23, 2009 [1 favorite]
To clarify:
=SUM('start here:end here'!A1)
That worked for me.
=SUM('start here':'end here'!A1)
This did not.
Also, moving the 'start here' sheet changed the result of the formula depending on what other sheets were in the range.
posted by bDiddy at 7:56 AM on December 23, 2009
=SUM('start here:end here'!A1)
That worked for me.
=SUM('start here':'end here'!A1)
This did not.
Also, moving the 'start here' sheet changed the result of the formula depending on what other sheets were in the range.
posted by bDiddy at 7:56 AM on December 23, 2009
I just checked my budget spreadsheet in Excel 2003 and Excel 2007, and used this formula to get my average income for each month in 2009:
=AVERAGE('jan 09:dec 09'!D69)
where xxx 09 was the name of each sheet and d69 was the target cell being averaged each time. You're using extra single quotes that aren't needed. You need just an open and close around the sheet range, then the bang and cell address.
Try this:
1) In your destination cell, type =average(
2) Go to the first sheet you're averaging from and click on the appropriate cell, such as H20
3) Hold your Shift key down and click on the tab for the last sheet that uses the same H20 cell. All the tabs in that series will be highlighted, selecting those cells in those sheets.
4) Press Enter. You will be back in your destination cell with a complete formula that works.
posted by maudlin at 10:50 AM on December 23, 2009 [1 favorite]
=AVERAGE('jan 09:dec 09'!D69)
where xxx 09 was the name of each sheet and d69 was the target cell being averaged each time. You're using extra single quotes that aren't needed. You need just an open and close around the sheet range, then the bang and cell address.
Try this:
1) In your destination cell, type =average(
2) Go to the first sheet you're averaging from and click on the appropriate cell, such as H20
3) Hold your Shift key down and click on the tab for the last sheet that uses the same H20 cell. All the tabs in that series will be highlighted, selecting those cells in those sheets.
4) Press Enter. You will be back in your destination cell with a complete formula that works.
posted by maudlin at 10:50 AM on December 23, 2009 [1 favorite]
« Older Do you know of somewhere funky to have a civil... | I don't get why aestheticians like their jobs! Newer »
This thread is closed to new comments.
posted by Nameless at 6:59 AM on December 23, 2009