Hello everyone,

I am having some trouble building a formula to find certain data. I think I am close, but I'm not there yet. I hope you can (and want to ) help me.

So, I have an array of numbers (differences from weight measurements of a mineral silo). The positive values indicate it being filled, negative numbers indicate a negative flow rate which means it's being emptied. Now, I want to know the averages of all sections between the negative values. Let's say I have this array (for simplification, starting at A2):

4

8

6

-8

9

1

2

2

-7

-2

6

4

I would like to know the averages of (in this case, starting at A2) index [2-4], [6-9], [12,13] and show these averages separately in a new list. Please note that these values vary everytime and therefore also the positions and number of negative values. I have already found out how to find and show all indices of all negatives values. I also know how to find averages of values between certain indices. My problem is that I have trouble skipping consecutive negative values, also because the positions vary. Sometimes I can have as much as 5 negative values in a row, it should skip all of them.

Array (!) formula to find negative indices: (it checks for negative values <-1 and lists them. If the list is done the main formula gives an error, which is ok but is then replaced by "".

=IF(ISERROR(SMALL(IF($A$1:$A$14:$A$1:$A$14<-1;ROW($A$1:$A$14);"");ROW(A1)));"";(SMALL(IF($A$1:$A$14:$A$1:$A$14<-1;ROW($A$1:$A$14);"");ROW(A1))))

For the list above this would result in these indices:

5

10

11

Function to find averages between indices: (which does not work for consecutive indices (like 10 and 11))

=IF((C1)="START";(AVERAGE($A$2:INDEX(A:A;$B$2-1)));AVERAGE(INDEX(A:A;B2+1):INDEX(A:A;B3-1)))

'START' is just used as a way to fix the first boundary. This returns 6 for index range 2-4 (correct), but -4,5 for the next one, which is wrong obviously.

Can you please help me to get this right? Many thanks in advance!

PS. please note my excel apparently (as the only one?) uses ";" instead of "," as a separator, just so you know (in case you want to test the functions above ).

## Bookmarks