+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    09-30-2013
    Location
    Hengelo, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    6

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

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-30-2013
    Location
    Hengelo, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    6

    Smile 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!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-30-2013
    Location
    Hengelo, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    6

    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.

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

    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.
    Attached Files Attached Files

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

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

    Quote Originally Posted by newdoverman View Post
    @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.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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))),"")
    Last edited by Tony Valko; 09-28-2014 at 09:14 PM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Registered User
    Join Date
    09-30-2013
    Location
    Hengelo, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    6

    Lightbulb 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).
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-30-2013
    Location
    Hengelo, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Any idea what the cause could be?

  11. #11
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,950

    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
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  12. #12
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

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

    Quote Originally Posted by zorba457773434 View Post
    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: copy to clipboard
    Please Login or Register  to view this content.

    and copy it down. i.e. make the N8 absolute.

  13. #13
    Registered User
    Join Date
    09-30-2013
    Location
    Hengelo, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    Many Thanks guys!

  14. #14
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,903

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to find single average the values datewise which has random dates?
    By lniraj in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-22-2014, 03:03 PM
  2. [SOLVED] Need to find average of an array if corresponding values are within a set range
    By Pallav001 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-21-2013, 03:06 AM
  3. [SOLVED] Formula needed to find AVERAGE over last 21 values > zero or null
    By Skidder in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-02-2012, 06:44 PM
  4. Assign fixed values to array / For...Next loop
    By SunOffice in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2011, 05:43 PM
  5. Replies: 2
    Last Post: 08-02-2006, 04:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1