Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
in short i am trying to use or create a formula that will give me the average
of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 ... the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
=Average(Indirect("G1:G"&E7))
-- regards, Tom Ogilvy "Response to nesting problem" wrote: in short i am trying to use or create a formula that will give me the average of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 .. the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
I appreciate the response how ever can you please explain this formula tome
so that I can understand how it works and what it does? Also when i pull up the formula bar it states that it is volitile...??? I am not sure what all this means please explain. "Tom Ogilvy" wrote: =Average(Indirect("G1:G"&E7)) -- regards, Tom Ogilvy "Response to nesting problem" wrote: in short i am trying to use or create a formula that will give me the average of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 .. the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
I believe Help should tell you whatever you need to know about how the INDIRECT function
works. Volatile means that the function will be recalculated whenever the worksheet is calculated, even if the value in E7 doesn't change. On Tue, 6 Nov 2007 15:19:01 -0800, Response to nesting problem m wrote: I appreciate the response how ever can you please explain this formula tome so that I can understand how it works and what it does? Also when i pull up the formula bar it states that it is volitile...??? I am not sure what all this means please explain. "Tom Ogilvy" wrote: =Average(Indirect("G1:G"&E7)) -- regards, Tom Ogilvy "Response to nesting problem" wrote: in short i am trying to use or create a formula that will give me the average of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 .. the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
There is just one problem with the formula that was given to me. It doesn't
work. Meaning that I have assigned the formula to the appropriate cell and the went to E7 and changed its value with the range of 1 to 300 and it keeps reporting the value of 150.5 and it doesnt change no matter what value is in E7. "Myrna Larson" wrote: I believe Help should tell you whatever you need to know about how the INDIRECT function works. Volatile means that the function will be recalculated whenever the worksheet is calculated, even if the value in E7 doesn't change. On Tue, 6 Nov 2007 15:19:01 -0800, Response to nesting problem m wrote: I appreciate the response how ever can you please explain this formula tome so that I can understand how it works and what it does? Also when i pull up the formula bar it states that it is volitile...??? I am not sure what all this means please explain. "Tom Ogilvy" wrote: =Average(Indirect("G1:G"&E7)) -- regards, Tom Ogilvy "Response to nesting problem" wrote: in short i am trying to use or create a formula that will give me the average of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 .. the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
Ok here is what my sheet looks like sorta to kind of give you an idea of what
i am trying to do http://gallery.photoshack.com/displa....php?pos=-6161 "Myrna Larson" wrote: I believe Help should tell you whatever you need to know about how the INDIRECT function works. Volatile means that the function will be recalculated whenever the worksheet is calculated, even if the value in E7 doesn't change. On Tue, 6 Nov 2007 15:19:01 -0800, Response to nesting problem m wrote: I appreciate the response how ever can you please explain this formula tome so that I can understand how it works and what it does? Also when i pull up the formula bar it states that it is volitile...??? I am not sure what all this means please explain. "Tom Ogilvy" wrote: =Average(Indirect("G1:G"&E7)) -- regards, Tom Ogilvy "Response to nesting problem" wrote: in short i am trying to use or create a formula that will give me the average of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 .. the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average the sum of numbers in a list or table
Ok here is what my sheet looks like sorta to kind of give you an idea of what
I am trying to do http://gallery.photoshack.com/displa....php?pos=-6162 "Myrna Larson" wrote: I believe Help should tell you whatever you need to know about how the INDIRECT function works. Volatile means that the function will be recalculated whenever the worksheet is calculated, even if the value in E7 doesn't change. On Tue, 6 Nov 2007 15:19:01 -0800, Response to nesting problem m wrote: I appreciate the response how ever can you please explain this formula tome so that I can understand how it works and what it does? Also when i pull up the formula bar it states that it is volitile...??? I am not sure what all this means please explain. "Tom Ogilvy" wrote: =Average(Indirect("G1:G"&E7)) -- regards, Tom Ogilvy "Response to nesting problem" wrote: in short i am trying to use or create a formula that will give me the average of the sum of numbers in a list or a table where the numbers to be added is based off a static cell that contains a number that will range between 1 and 300. ie... static cell(E7) will contain the number 1-300 .. the table or list will also have the corresponding 1-300 i want the formula to look like this... if(e7=93,avg(sum(g1:g93))) but the problem is I dont want to use the IF formula i want it just to do the average based on E7... It sees that e7 is 93 and knows only to sum 1-93 and then give me the average of the sum and if the number in e7 should change it will recalculate and give a new answer. Now I am pretty new to this kind of programming/formulas and am at a stand still and do not know what to do. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average a continuous group of numbers and negative numbers are 0 | Excel Worksheet Functions | |||
How to average numbers greater than zero in a pivot table | Excel Worksheet Functions | |||
Average highest 16 numbers on a column of 32 numbers | Excel Worksheet Functions | |||
Finding an average of a list that could have 0 to 10 numbers in it | Excel Discussion (Misc queries) | |||
Average the last 7 numbers on a list | Excel Worksheet Functions |