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.

=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.

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.

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
<(E-Mail Removed)> 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.

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
> <(E-Mail Removed)> 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.
>

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
> <(E-Mail Removed)> 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.
>

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
> > <(E-Mail Removed)> 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.
> >