Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing named range reference depending on a cell's content | Excel Discussion (Misc queries) | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
changing a range of fields | Excel Discussion (Misc queries) |