Formula to find average of values between negative values in 'random' array of fixed size

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 ).

Hi!,

1] sorry for the trouble, I changed it (although I could have really been on the North Pole of course )
2] I have uploaded it, but unfortunately, my excel version is Dutch, so I don't know how well that works..? I translated the functions I posted here for convenience.
I have included the example (for simplification) and the actual data problem this is for as well. Thanks in advance!

Hey guys,

any ideas on how to approach this? Maybe using IF-statements, or by using some kind of counter for every negative value maybe? Any help would be greatly appreciated. The attached files show the problem and the currently used functions.
Thanks.

Dear Richard and Tony, many thanks for your help!
It is very helpful to see the use of functions I didn't know before.

However, I have one small question (for Richard ). I loaded some data from Juli and now something goes wrong. The first block of positive numbers is just one number. The second block of positive numbers is much larger, but the ending index of this block is missed. Specifically in Column K, Row 9 it should say '32' but this index is skipped entirely. The rest seems to be ok (apart from the now disturbed sequence). Strange, because the method works for all other values and nothing special happens at index 32 (other than the change from positive to negative).I don't see why it doesn't work. Could you please a look at it? Thanks again!

I included the file with the new data (V2).

Any idea what the cause could be?

Many Thanks guys!