Averages function
Posted by Dave Benham on June 09, 2001 8:22 AM
I have a question regarding Excel that I've been trying to come up with an answer to - and failing.
I have a spreadsheet of information that is sorted alphabetically, and by date. The last column provides a list of data that I wish to know the average of.
I am aware that this can be done in Excel, but this is thrown out of synch when new data is added to the top of a previous listing.
My spreadsheet has 1300 entries at present, with approximately 25 new entries being made each day.
For instance, I want to average cells p7 - p18 - which is easily accomplished, but then a new alphabetical entry to be included in the average goes in at p6 and it all has to be redone. Is there a way to do this?
Posted by Aladin Akyurek on June 09, 2001 8:52 AM
Dave
Are you trying to average everything in column P or a subpart of it?
Problem 1. lets say that you have values in P1:P100 at day 1. You average this with =AVERAGE(P1:P100). On Day 2, 10 new values are added to column P and other info to other columns. The formula misses of course these 10 new values.
Problem 2. Lets say that you are averaging (for some good reason) a subrage of values in P, say =AVERAGE(P7:P18). When new values added and everything is resorted, =AVERAGE(P7:P18) does not average the values that it should because some values of interest now fall out of the range of the AVERAGE formula.
Which of these problems do you have?
Aladin
I have a spreadsheet of information that is sorted alphabetically, and by date. The last column provides a list of data that I wish to know the average of. I am aware that this can be done in Excel, but this is thrown out of synch when new data is added to the top of a previous listing. My spreadsheet has 1300 entries at present, with approximately 25 new entries being made each day. For instance, I want to average cells p7 - p18 - which is easily accomplished, but then a new alphabetical entry to be included in the average goes in at p6 and it all has to be redone. Is there a way to do this?
Posted by Mark W. on June 09, 2001 1:00 PM
=AVERAGE(INDIRECT("P7"):P18) I have a spreadsheet of information that is sorted alphabetically, and by date. The last column provides a list of data that I wish to know the average of. I am aware that this can be done in Excel, but this is thrown out of synch when new data is added to the top of a previous listing. My spreadsheet has 1300 entries at present, with approximately 25 new entries being made each day. For instance, I want to average cells p7 - p18 - which is easily accomplished, but then a new alphabetical entry to be included in the average goes in at p6 and it all has to be redone. Is there a way to do this?
