Microsoft Excel 2007 Help

exalted512
exalted512 Posts: 10,735
edited February 2011 in The Clubhouse
I've searched online and through help and for the life of me can't figure out how to average a single cell across 20ish sheets.

I have a budget thing I setup on excel, and I'm trying to find my average spending for last year across my different categories.

The sheets' names are dates:

The first sheet I have is called: "3.29-4.11" and the last sheet is "12.21-1.5" and I want to make an 'Averages' sheet, and for instance, one of the cells I want to average is I49.

Right now, I'm having to click on each sheet on each cell, is there an easier way to do this?

Here's my formula as of now:
=AVERAGE('5.10-5.23'!I49, '5.24-6.5'!I49, '6.6-6.20'!I49, '6.21-7.5'!I49, '7.6-7.25'!I49, '7.26-8.5'!I49, '8.6-8.20'!I49, '8.21-9.5'!I49, '9.6-9.20'!I49, '9.21-10.5'!I49, '10.6-10.20'!I49, '10.21-11.5'!I49)

It works, just looking for something easier.
-Cody
Music is like candy, you have to get rid of the rappers to enjoy it
Post edited by exalted512 on
· Share on FacebookShare on Twitter

Comments

  • JimKellyfan
    JimKellyfan Posts: 696
    edited February 2011
    Almost sounds like pivot table learning.
    Microsoft offers free training online.
    I have 10 now, so research on my end would be futile for you.
    Sorry I couldn't be of more help.
    B ut the help menu / training is very helpful.
    Onkyo TX-NR636
    Main - polkaudio Monitor 60's
    Center - polkaudio CSI A6
    Sub - polkaudio PSW10
    Sub2 - polkaudio PSW505
    Surrounds - polkaudio Monitor 40 series II
    Front Height - polkaudio Monitor 40's
    Audio Outdoors
    Pioneer VSX 406 - polkaudio Atrium 5
    Shed
    Sony junker str-dn2010 with Sony bookshelf floor models and polkaudio R150's
    Shed 2
    Nakamichi soundbar
    · Share on FacebookShare on Twitter
  • spock 2054
    spock 2054 Posts: 163
    edited February 2011
    See if the link below works for you...

    http://www.eggheadcafe.com/software/aspnet/35569113/average-nonzero-cells-across-several-worksheets-in-same-workbook.aspx

    Try this...

    =SUM(Sheet1:Sheet54!K3)/INDEX(FREQUENCY(Sheet1:Sheet54!K3,0),2)
    polk monitor 70's
    center - polk monitor cs2
    surround - polk monitor 60's
    surround back - jbl e10
    sub - velodyne dps 12
    sub - polk psw110
    avr/pre-amp - onkyo tx-nr809
    amp - adcom gfa-5500
    amp - carver av405
    display - sharp lc70le847u
    tv - silicon dust hd homern
    blu-ray - oppo bdp-103
    hd dvd- toshiba hd xa2
    control - logitech harmony one
    turntable - technics sl1500 mkII
    · Share on FacebookShare on Twitter
  • irishaz
    irishaz Posts: 161
    edited February 2011
    I would recommend that if you want to keep your separate tabs, add a tab that is cumulative and just add a column for each time period that you want to track (weekly, bi-weekly, monthly, etc). I start most of my spreadsheets with year, month, week-ending, and date columns - I have to compile a lot of payroll data. Excel doesn't really like separate tabs - at least not for pivot tables. Once you have a cumulative tab - you can just do a simple pivot table to give you averages for each time period. Pivot tables are a great tool - and not hard to learn at all with a little patience. Pivot charts come right after that - again a very effective tool.
    · Share on FacebookShare on Twitter