+ Reply to Thread
Results 1 to 16 of 16

automate average calculation

  1. #1
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    automate average calculation

    I have a list of several hundred numbers, with one number added each day. Occasionally a number that is added is higher than all previous numbers. I am interested in calculating the average number of rows between these new record highs.

    For example, in the list 0, 3, 5, -1, 0, 4, 9, 8, 7:
    -- the new record highs are 3, 5 and 9
    -- and the # of rows separating these records are, respectively, 1, 1 and 4 - for an average of (1+1+4)/3=2.

    I currently calculate this in the following manner:
    The numbers are in range A2:A10.
    In cell B2, I have this formula, which I copy down:
    =IF(A2=MAX(A$2:A2),ROW()-LOOKUP(2,1/(A1:A$2=LARGE(A1:A$2,1)),ROW(A1:A$2)),"")

    And in cell F2, I have this formula:
    =AVERAGEIF(B2:B10,">0")

    That gives me the correct answer, but I want to eliminate the need for the column of data in column B. In other words, I want to automate this calculation so that with each daily entry in column A, my answer in F2 will be updated automatically.

    Any ideas?

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: automate average calculation

    As my knowdledge, with formula, it is too complicate without helper column. VBA may helps? I will ask for other help.
    Quang PT

  3. #3
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    thank you very much

  4. #4
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,340

    Re: automate average calculation

    you would probably need VBA
    In case that is workable - I threw this together - hopefully it'd work

    Please Login or Register  to view this content.
    If you want something done right... find a forum and ask an online expert.

    Time flies like an arrow. Fruit flies like a banana.

  5. #5
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    Awesome -- many thanks!
    Now all I have to do is learn how to use this code. I know zero about VBA, so that is now my challenge.
    Thank you very much.

  6. #6
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: automate average calculation

    I was toying with this yesterday and came up with the following formula, which appears to be holding up. If your data is in A2:A10, array-enter (confirm with Ctrl + Shift + Enter instead of Enter) the formula below:

    =AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A10)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A10)-1,1))>0,ROW(A2:A10)-2,0),ROW(A1:A8)+1)))

    It seems to work for me, but if you need to be able to alter or adapt it with any frequency, it might be advisable to stick with the simpler helper column approach or use scottiex's VBA solution.
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  7. #7
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    CAntosh: many thanks. I tried your idea and it does seem to work for the range a2:a10.

    But unfortunately it does not update automatically when new values are added to the A column.

  8. #8
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: automate average calculation

    Sorry, Mike, I should have clarified - It's only written for A2:A10 in post#6, but if you want to be able to add numbers, just make the range much bigger than your data. Change the A10s to something huge, and change the A8s to two less than that. For instance, array-entering:

    =AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A2,0,0,ROW(A2:A1000)-1,1))-SUBTOTAL(4,OFFSET(A1,0,0,ROW(A2:A1000)-1,1))>0,ROW(A2:A1000)-2,0),ROW(A1:A998)+1)))

    ...will automatically incorporate new rows up to row 1000.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    Amazing -- yes that does work!

    Will use that.

    At the same time, now I have an incentive to learn some VBA basics to try scottiex's idea.

    Thanks to all.

    Mike_F

  10. #10
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: automate average calculation

    Glad to help! And I strongly endorse developing a grasp of some basic VBA. There's a thread over in the VBA section with some excellent resources when you're ready to take the plunge:

    https://www.excelforum.com/excel-pro...materials.html
    Last edited by CAntosh; 05-05-2017 at 01:35 PM.

  11. #11
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    great, tnx -- will check those out.

  12. #12
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: automate average calculation

    OP noted in a PM that an error results from inserting a new first row. This is a salient point I should've covered for any wandering across this thread. My response:

    If you insert a new Row 1, Excel will automatically shift the ranges in my formula down a row. This adjustment is exactly what we want for the A2:A1000s, but not for the A1:A998s. For the formula to work, the A1:A998 range should always be one cell smaller than the big range of data (A2:1000) in size and it should always start in Row 1. If you're inserting a new row 1 and bumping your data down row, then the new formula you want would be:

    =AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A3,0,0,ROW(A3:A1001)-1,1))-SUBTOTAL(4,OFFSET(A2,0,0,ROW(A3:A1001)-1,1))>0,ROW(A3:A1001)-2,0),ROW(A1:A998)+1)))

    So after inserting the row you would need to manually change the A2:A999s to A1:A998s in the formula or use a 'Replace All' to get them all at once (or copy/paste the above and array-confirm it). I could try to rewrite the formula so it responds better to Excel's IntelliSense updating, but I didn't want to make the formula uglier to parse than it already is. Stick to the "one smaller and starting in row 1" rule and you should be fine.

  13. #13
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    A further update to this thread:

    After inserting one row, and changing the A2:A999s to A1:A998s, all is fine. The calculation produces the same result (in the example: 2), as it should.

    But after inserting another row (and again changing the A2:A999s to A1:A998s, the calculation produces a different result: 3. This leaves me stumped.

    Here's the formula I'm using after inserting the second row:

    =AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-1,1))>0,ROW(A4:A1002)-2,0),ROW(A1:A998)+1)))

    Mike_F

  14. #14
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: automate average calculation

    Good catch - that's one of those adaptability modifications I should have built in earlier. Basically, the "-1"s and "-2"s in the formula are based on the starting row, and I didn't build them to be relative in the original formula, so that part of the formula didn't adapt well to a new starting row. Sorry for the oversight. The "-1" parts should become, in your current example: "-ROW(A4)+1" and the "-2" parts should become "-ROW(A4)". They were actually wrong after your first inserted row, but the result wasn't affected. So the whole formula, array-entered, should be:

    =AVERAGE(IF(LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998)+1)>0,LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998))-LARGE(IF(SUBTOTAL(4,OFFSET(A4,0,0,ROW(A4:A1002)-ROW(A4)+1,1))-SUBTOTAL(4,OFFSET(A3,0,0,ROW(A4:A1002)-ROW(A4)+1,1))>0,ROW(A4:A1002)-ROW(A4),0),ROW(A1:A998)+1)))

    You'll still need to adjust the A1:A998 section manually when you insert a row, but the rest should update accordingly. Let me know if the new formula gives you any wonky results.

  15. #15
    Registered User
    Join Date
    05-04-2017
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    17

    Re: automate average calculation

    That has nailed it!

    Works perfectly in my workbook. Have tested it in multiple ways. Excel is amazing.

    Many thanks.

  16. #16
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: automate average calculation

    My pleasure!

+ 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. To automate Calculation of time taken
    By rshnkmr39 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2014, 03:00 PM
  2. HELP: macro to automate a calculation
    By sampsulo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2013, 09:05 AM
  3. Excel 2007 : How to automate calculation of threshold value?
    By sandyjune1988 in forum Excel General
    Replies: 1
    Last Post: 12-28-2011, 02:50 AM
  4. Automate Calculation with help of VBA
    By Dongfang in forum Excel General
    Replies: 2
    Last Post: 03-11-2011, 04:29 AM
  5. [SOLVED] How to automate the calculation of the median from a frq distribut
    By Alaska Hydro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2005, 09:10 PM
  6. [SOLVED] Automate simple calculation/formula
    By Leslie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  7. [SOLVED] Help required to automate calculation
    By Tom in forum Excel General
    Replies: 1
    Last Post: 02-02-2005, 10:06 AM

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