And maybe something with dollar signs I don't know
May 16, 2012 11:15 AM Subscribe
Need help with (possibly?) "indirect" in Excel.
posted by phunniemee to computers & internet (21 answers total) 1 user marked this as a favorite
I keep track of some things daily at work in an Excel file. The data is set up like this:
I have this going back every day for months.
And I also have several (nine) different pages of the same thing with the same structure to keep track of these numbers for several different individuals.
I need to be able to keep a running total on a summary page (in that same format) BOTH for each individual AND everyone added together for the following things:
2) Average of the previous week (previous 5 days irrespective of date)
3) Average of the previous month (previous 20 days irrespective of date)
4) Total of a particular, dated month (e.g. January 1-January 31)
5) Grand total
And I need it every day. (Which means 1-3 and 5 will change, and 4 will be static.)
I have no idea how to do this. Each new day adds to the top of the sheet, shifting the previous day(s) down each time. I'm pretty sure I need to use the "indirect" function somehow to make sure that A1 always refers to cell A1, even if the data in A1 shifts down to A2. I just don't know how to use it on so large a scale, taking 100s of cells into account (and summing/averaging them).
I've read pretty much every googlable thing on this and I can't seem to jigger something that works. I am scared and alone in a useless field of data. HELP.