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
|