Changing the range of several averaging functions
 

Hi guys

I have a spreadsheet that has a number of data. Beneath the data I have a 10
averaging functions that average data on different columns.

The data describe events.

Each event is assigned the number zero therefore I want to exclude those
values from the averaging functions (the number zero could be a result of the
event).

At the moment my average functions will state for example
AVERAGE(E6,E10)
When the event happens then I want it to become
AVERAGE(E6,E10,E13)
(Note: the averaging is not sequential i.e. 11 is not after 10, it could
jump numbers)

What I want to do is because I have 10 averaging functions that all must
include the result from the same event I want as soon as I enter E13, for the
function with for example
AVERAGE(G6,G10) to become AVERAGE (G6,G10,G13)
and for the function with
AVERAGE(Z6,Z10) to become AVERAGE (Z6,Z10,Z13)

etc...

I believe I have been clear enough but please let me know if this is not the
case.

Thanks

Since the increment varies (randomly???), I'm not sure how you would do this
without some kind of indicator of which rows to include.

I used a helper column (D) and I could put an X in that cell if I want that row
included.

Then I could use a formula like:

=SUMPRODUCT(--(D2:D99="X"),(E2:E99))/SUMPRODUCT(--(D2:D99="X"),--(E2:E99<0))

The numerator sums up all the values in E2:E99 that have X in column D of their
corresponding row.

The denominator counts then number of non-zeros you have have.



Hellion wrote:

Hi guys

I have a spreadsheet that has a number of data. Beneath the data I have a 10
averaging functions that average data on different columns.

The data describe events.

Each event is assigned the number zero therefore I want to exclude those
values from the averaging functions (the number zero could be a result of the
event).

At the moment my average functions will state for example
AVERAGE(E6,E10)
When the event happens then I want it to become
AVERAGE(E6,E10,E13)
(Note: the averaging is not sequential i.e. 11 is not after 10, it could
jump numbers)

What I want to do is because I have 10 averaging functions that all must
include the result from the same event I want as soon as I enter E13, for the
function with for example
AVERAGE(G6,G10) to become AVERAGE (G6,G10,G13)
and for the function with
AVERAGE(Z6,Z10) to become AVERAGE (Z6,Z10,Z13)

etc...

I believe I have been clear enough but please let me know if this is not the
case.

Thanks

--

Dave Peterson