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

1. ## 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.

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 ).  Register To Reply

2. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

Hi,

Are you able to upload the workbook so that we can see the request in context?

Whilst I'm here, would you change the location in your profile to something sensible please. We ask these things for a reason.  Register To Reply

3. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

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!  Register To Reply

4. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

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.  Register To Reply

5. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

Hi,

See attached

Columns L&M are helper columns. Ignore N&O these were just my manually calculated check rows numbers.  Register To Reply

6. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

@Richard Buttrey

That is a nice way of separating the ranges of positive values.  Register To Reply

7. ## Re: Formula to find average of values between negative values in 'random' array of fixed s Originally Posted by newdoverman @Richard Buttrey

That is a nice way of separating the ranges of positive values.
Thanks ND.

Must admit it took me a lot of experimentation to figure out a reasonably practical solution that wasn't too opaque with loads of array formulae.
But I guess we all go through these trade offs all the time.   Register To Reply

8. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

Here's another option...

Data Range
 A B C D 1 Reading Average ------ All Averages 2 4 6 6 3 8 3.5 4 6 5 5 -8 6 9 3.5 7 1 8 2 9 2 10 -7 11 -2 12 6 5 13 4 14

Enter this array formula** in B2:

=IF(A2<0,"",AVERAGE(A2:INDEX(A2:A14,MATCH(1,(A2:A14<0)+(A2:A14=""),0)-1)))

Enter this array formula** in B3 and copy down as needed:

=IF(A3<0,"",IF(A2>=0,"",AVERAGE(A3:INDEX(A3:A\$14,MATCH(1,(A3:A\$14<0)+(A3:A\$14=""),0)-1))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Note that the array formulas reference down to row 14 but the data ends on row 13.

Enter this formula in D2 and copy down until you get blanks:

=IFERROR(INDEX(B:B,AGGREGATE(15,6,1/(B\$2:B\$13<>"")*ROW(B\$2:B\$13),ROWS(D\$2:D2))),"")  Register To Reply

9. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

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).  Register To Reply

10. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

Any idea what the cause could be?  Register To Reply

11. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

see the attached file.
one helper column was created with yellow color  Register To Reply

12. ## Re: Formula to find average of values between negative values in 'random' array of fixed s Originally Posted by zorba457773434 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 see a small logic errror in the formula that starts in L8. Change this to

Formula:  `Please Login or Register  to view this content.`

and copy it down. i.e. make the N8 absolute.  Register To Reply

13. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

Many Thanks guys!  Register To Reply

14. ## Re: Formula to find average of values between negative values in 'random' array of fixed s

Glad to have been able to help and thanks for the rep.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 