|
average function in Excel 2002
Hello all!
I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
Define a dynamic range...
Insert Name Define Name: Data Refers to: =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Click Ok Then use the following formula... =AVERAGE(Data) Hope this helps! In article , Sherry wrote: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
I risk causing some upset here but...............Ummmmm
What does that all mean Domenic? Some dumb questions to follow!! Which cell do I name Data??? Where do I place =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) ???? Where do I use the formula... =AVERAGE(Data) Perhaps I should have included the fact that I am very new to this functions and macro stuff. A point that I am sure after this post will become painfully obvious. LOL "Domenic" wrote: Define a dynamic range... Insert Name Define Name: Data Refers to: =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Click Ok Then use the following formula... =AVERAGE(Data) Hope this helps! In article , Sherry wrote: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
Well, you posted to the .newusers group, so don't be shy about asking
follow-ups. Dominic was suggesting that you define a dynamic range. That means you don't name specific cells. Instead, choose the Insert/Name/Define menu item. In the "Names in workbook" textbox enter "Data" (without the quotes). In the "Refers to:" textbox, enter the formula, then click "Add". You can use Dominic's formula, or, if you don't have any blank rows, this formula will also work: =OFFSET(Sheet1!$D$13,0,0,COUNTA(Sheet1!$D$13:$D$65 536),1) Adjust the references for your sheet (e.g., if you sheet is named "Staff", use =OFFSET(Staff!$D$13... For an alternative explanation, see http://cpearson.com/excel/named.htm#Dynamic In article , Sherry wrote: I risk causing some upset here but...............Ummmmm What does that all mean Domenic? Some dumb questions to follow!! Which cell do I name Data??? Where do I place =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) ???? Where do I use the formula... =AVERAGE(Data) Perhaps I should have included the fact that I am very new to this functions and macro stuff. A point that I am sure after this post will become painfully obvious. LOL |
Hi Sherry!
No problem, let me gear down a bit... :) First we need to define a dynamic range. In doing so, the range will automatically adjust as new data is entered. But we'll need to name this range and provide a reference for it. Go to the top of your Excel menu and select Insert. Then select Name, and then Define. A new window will open. Where it says 'Name:', enter the name you wish to use for this range. In my example, I used Data, but you can use whichever name you wish. Once you've entered the name, enter the reference where it says 'Refers to:'. The reference would be... =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Once you've entered the reference, click OK. Now you can use the following formula to give you your average... =AVERAGE(Data) So as you can see, the range we're using for the AVERAGE function is Data, which we defined in the first step. Hope this helps! In article , Sherry wrote: I risk causing some upset here but...............Ummmmm What does that all mean Domenic? Some dumb questions to follow!! Which cell do I name Data??? Where do I place =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) ???? Where do I use the formula... =AVERAGE(Data) Perhaps I should have included the fact that I am very new to this functions and macro stuff. A point that I am sure after this post will become painfully obvious. LOL "Domenic" wrote: Define a dynamic range... Insert Name Define Name: Data Refers to: =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Click Ok Then use the following formula... =AVERAGE(Data) Hope this helps! In article , Sherry wrote: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
Sherry
An easy way that doesn't use any formula. Right-click the Status Bar, and put a checkmark next to 'Average'. Highlight the cells you wish to average, the average of these cells will be displayed on the Status Bar! George Gee *Sherry* has posted this message: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
Thank you.This has acheived my objective and more importantly helped me to
understand what I am doing. I am most grateful and suitably impressed with the amount of interest and support that was forthcoming in such a timely manner. I know I will have many more challenges to overcome during this learning curve so I am sure I will post again soon. TY Cheers Sherry "Domenic" wrote: Hi Sherry! No problem, let me gear down a bit... :) First we need to define a dynamic range. In doing so, the range will automatically adjust as new data is entered. But we'll need to name this range and provide a reference for it. Go to the top of your Excel menu and select Insert. Then select Name, and then Define. A new window will open. Where it says 'Name:', enter the name you wish to use for this range. In my example, I used Data, but you can use whichever name you wish. Once you've entered the name, enter the reference where it says 'Refers to:'. The reference would be... =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Once you've entered the reference, click OK. Now you can use the following formula to give you your average... =AVERAGE(Data) So as you can see, the range we're using for the AVERAGE function is Data, which we defined in the first step. Hope this helps! In article , Sherry wrote: I risk causing some upset here but...............Ummmmm What does that all mean Domenic? Some dumb questions to follow!! Which cell do I name Data??? Where do I place =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) ???? Where do I use the formula... =AVERAGE(Data) Perhaps I should have included the fact that I am very new to this functions and macro stuff. A point that I am sure after this post will become painfully obvious. LOL "Domenic" wrote: Define a dynamic range... Insert Name Define Name: Data Refers to: =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Click Ok Then use the following formula... =AVERAGE(Data) Hope this helps! In article , Sherry wrote: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
Thank You also JE McGimpsey
The site that you referenced is excellent. I found the writting style to be very well laid out and easy to understand. (It's in my favourites folder now) Thank You JE "JE McGimpsey" wrote: Well, you posted to the .newusers group, so don't be shy about asking follow-ups. Dominic was suggesting that you define a dynamic range. That means you don't name specific cells. Instead, choose the Insert/Name/Define menu item. In the "Names in workbook" textbox enter "Data" (without the quotes). In the "Refers to:" textbox, enter the formula, then click "Add". You can use Dominic's formula, or, if you don't have any blank rows, this formula will also work: =OFFSET(Sheet1!$D$13,0,0,COUNTA(Sheet1!$D$13:$D$65 536),1) Adjust the references for your sheet (e.g., if you sheet is named "Staff", use =OFFSET(Staff!$D$13... For an alternative explanation, see http://cpearson.com/excel/named.htm#Dynamic In article , Sherry wrote: I risk causing some upset here but...............Ummmmm What does that all mean Domenic? Some dumb questions to follow!! Which cell do I name Data??? Where do I place =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) ???? Where do I use the formula... =AVERAGE(Data) Perhaps I should have included the fact that I am very new to this functions and macro stuff. A point that I am sure after this post will become painfully obvious. LOL |
Thankyou George,
I wasn't aware that existed. (I am not game to reveal how I was counting cells before this) Best Regards, Rodney | Sherry | | An easy way that doesn't use any formula. | Right-click the Status Bar, and put a checkmark next to 'Average'. | Highlight the cells you wish to average, the average of these cells | will be displayed on the Status Bar! | | George Gee | | | | *Sherry* has posted this message: | | Hello all! | I wish to obtain the average of a number of numeric cells but wish | this average to be ongoing rather than restricted to a range of cells. | | For example | =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 | but I will be continually adding more data and wish for this to be | calculated in the average also. | | My overall objective here is to carry out evaluations on staff | performance and as each new review arrives from a client regarding a | staff member, I will be adding a new row to include this | data.Therefore need to have the number of all reviews received | whether it be five or fifty to be calculated in the overall average. | | I have not been able to identify anything in the application help to | cover this use of the average function and help will be greatfully | appreciated | | Cheers Sherry | | |
Rodney
Being only an 'average' user of Excel, myself, it is sometimes the simple things that get overlooked by the 'experts', that are of some use to us 'mere mortals'! Let me guess how you were counting cells... one, two, three, four........? Good to know I have helped someone! George Gee *Rodney* has posted this message: Thankyou George, I wasn't aware that existed. (I am not game to reveal how I was counting cells before this) Best Regards, Rodney Sherry An easy way that doesn't use any formula. Right-click the Status Bar, and put a checkmark next to 'Average'. Highlight the cells you wish to average, the average of these cells will be displayed on the Status Bar! George Gee *Sherry* has posted this message: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
G'day George,
we have been, for many years, power users of MSWorks we spend hours, manipulating data, reporting etc and MSWorks is a dandy way for the intellectually challenged to really get some distance out of data. We were always hamstrung by the 30,000 records of the Works database, (albeit we love the "flat file" configuration) and since dipping the toe into Excel we have been getting excited. Our main challenge is to extract relevent data from our main VFP dtabase now. We use Excel only as a database at the moment and the little trick you enlightened us with, assists the "what if's" we use. We work with around 45,000 rows, and we were having to select the relevent range and do "sum" calculations etc. not knowing this handy trick was a button away. If we had an image of yourself, we would probably be lighting candles and paying due homage at relevent times :) | Rodney | | Being only an 'average' user of Excel, myself, it is sometimes the simple | things that get overlooked by the 'experts', that are of some use to us | 'mere mortals'! | | Let me guess how you were counting cells... one, two, three, four........? | | Good to know I have helped someone! | | George Gee | | | | *Rodney* has posted this message: | | Thankyou George, | I wasn't aware that existed. | (I am not game to reveal how I was counting cells before this) | Best Regards, | Rodney | | | | | Sherry | | An easy way that doesn't use any formula. | Right-click the Status Bar, and put a checkmark next to 'Average'. | Highlight the cells you wish to average, the average of these cells | will be displayed on the Status Bar! | | George Gee | | | | *Sherry* has posted this message: | | Hello all! | I wish to obtain the average of a number of numeric cells but wish | this average to be ongoing rather than restricted to a range of | cells. | | For example | =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 | but I will be continually adding more data and wish for this to be | calculated in the average also. | | My overall objective here is to carry out evaluations on staff | performance and as each new review arrives from a client regarding a | staff member, I will be adding a new row to include this | data.Therefore need to have the number of all reviews received | whether it be five or fifty to be calculated in the overall average. | | I have not been able to identify anything in the application help to | cover this use of the average function and help will be greatfully | appreciated | | Cheers Sherry | | | |
My appologies George Gee
Thank You also for your interest in my problem. When first I read your post i mistakenly thought you misunderstood my request but of course I was wrong. Your tip is indded a valuable one and I chuckle I little now when I highligh a column and see the Average function pop its head up in my status bar. Thank you "George Gee" wrote: Sherry An easy way that doesn't use any formula. Right-click the Status Bar, and put a checkmark next to 'Average'. Highlight the cells you wish to average, the average of these cells will be displayed on the Status Bar! George Gee *Sherry* has posted this message: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
Domenic!
Could you please clarify for me what the reference to the number 65536 that is part of the function does and why that particular number? Is that how many rows a spreadsheet can hold? And again for the reference to (9.99999999999999 and 307? What is the purpose and role of these numbers in the overall reference? Sorry to be a pest but I although I have adopted the reference, I do not comprehend how and why it works! So if you are so inclined, I would appreciate your elaboration here. PS: I cerainly realise the value of short names now given that my sheet name was OperatorEvaluation. Cheers Sherry "Domenic" wrote: Hi Sherry! No problem, let me gear down a bit... :) First we need to define a dynamic range. In doing so, the range will automatically adjust as new data is entered. But we'll need to name this range and provide a reference for it. Go to the top of your Excel menu and select Insert. Then select Name, and then Define. A new window will open. Where it says 'Name:', enter the name you wish to use for this range. In my example, I used Data, but you can use whichever name you wish. Once you've entered the name, enter the reference where it says 'Refers to:'. The reference would be... =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Once you've entered the reference, click OK. Now you can use the following formula to give you your average... =AVERAGE(Data) So as you can see, the range we're using for the AVERAGE function is Data, which we defined in the first step. Hope this helps! In article , Sherry wrote: I risk causing some upset here but...............Ummmmm What does that all mean Domenic? Some dumb questions to follow!! Which cell do I name Data??? Where do I place =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) ???? Where do I use the formula... =AVERAGE(Data) Perhaps I should have included the fact that I am very new to this functions and macro stuff. A point that I am sure after this post will become painfully obvious. LOL "Domenic" wrote: Define a dynamic range... Insert Name Define Name: Data Refers to: =Sheet1!$D$13:INDEX(Sheet1!$D$13:$D$65536,MATCH(9. 99999999999999E+307,She et1!$D$13:$D$65536)) Click Ok Then use the following formula... =AVERAGE(Data) Hope this helps! In article , Sherry wrote: Hello all! I wish to obtain the average of a number of numeric cells but wish this average to be ongoing rather than restricted to a range of cells. For example =AVERAGE(D13:D17) will only give the average of the cells D13 to D17 but I will be continually adding more data and wish for this to be calculated in the average also. My overall objective here is to carry out evaluations on staff performance and as each new review arrives from a client regarding a staff member, I will be adding a new row to include this data.Therefore need to have the number of all reviews received whether it be five or fifty to be calculated in the overall average. I have not been able to identify anything in the application help to cover this use of the average function and help will be greatfully appreciated Cheers Sherry |
In article ,
Sherry wrote: Could you please clarify for me what the reference to the number 65536 that is part of the function does and why that particular number? Is that how many rows a spreadsheet can hold? Yes, it's the total number of rows a spreadsheet can hold. That number was used so that the dynamic range would extend all the way down to the end of the spreadsheet. One can always reduce the range by changing that number. And again for the reference to (9.99999999999999 and 307? What is the purpose and role of these numbers in the overall reference? It's the largest number that Excel recognizes. In this case, it's used as a lookup value for the MATCH function. It returns the position of the last numerical value in the range of cells specified in the MATCH function. Hope this helps! |
| All times are GMT +1. The time now is 07:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com