Changing the range of several averaging functions

[Guest]

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]

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(--(D299="X"),(E2:E99))/SUMPRODUCT(--(D299="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.