+ Reply to Thread
Results 1 to 12 of 12

Subtotal "average" with criteria from another column

  1. #1
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Subtotal "average" with criteria from another column

    Hello forum,
    Have this little problem to find out the average speed within a route. In column B is the sequence of the route so sequence no 1 is the first stop etc. After first stop the route begins so I want to find the average speed within the route in other word every sequence 1 has to be out of the calculation. I want the possibility to filter each route to see average speed within the route.

    Attached is a test file, I tried using "=SUMPRODUCT(SUBTOTAL(1," but have problems for it to work using function "average"

    Thanks
    Attached Files Attached Files
    Last edited by soreno; 08-27-2018 at 04:25 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Subtotal "average" with criteria from another column

    Try this:

    =AVERAGE(IF(SUBTOTAL(2,OFFSET(C3,ROW(C3:C2574)-ROW(C3),0,1)),IF(B3:B2574<>1,C3:C2574))) Ctrl Shift Enter

  3. #3
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Re: Subtotal "average" with criteria from another column

    hmm getting error trying..., I need this to work in vba as other things need to be processed as well, any suggestion for solution in vba?
    Thanks

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Subtotal "average" with criteria from another column

    Are you confirming the formula with Ctrl Shift Enter instead of just Enter? It works as expected on my end.

    I must have missed that you were looking for a VBA solution.

    There was already a formula in the cell. I figured that you were looking for a formula solution.

    Once this formula is in the cell, you should never have to touch it.

    I'm not sure why you say that you need VBA for this but regardless, VBA is not my specialty so I won't be able to help there.

  5. #5
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Re: Subtotal "average" with criteria from another column

    Tried it with my vba macro, but it did nothing...

    Please Login or Register  to view this content.
    This one is working but that is not an average value

    Please Login or Register  to view this content.

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Subtotal "average" with criteria from another column

    Copy and paste the formula from post #2 into C1 of the sample that you shared in post #1 and confirm it using Ctrl Shift Enter.

    You should get 31.96428...

    Once that you see that the formula works, you can work on entering it using VBA.

    Maybe you enter an array formula differently than a regular formula in VBA?

  7. #7
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Re: Subtotal "average" with criteria from another column

    I'm using mac for the moment and I tried all combinations of Ctrl Shift Enter, but it gives me an error pointing to the "OFFSET" line... Array has been working before I need to check if there is another combination for mac I have missed....

  8. #8
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Subtotal "average" with criteria from another column

    I don't use a mac. After a bit of Googling, try ⌘ + RETURN or CTRL + SHIFT + RETURN.

  9. #9
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Re: Subtotal "average" with criteria from another column

    OOPPS sorry, I know the problem now... I forgot to change the "," to ";" we use ";" instead in formulas... now it is working, but I do see you use 2 after SUBTOTAL and that is normally "Count"..!? Why not 1?

  10. #10
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Re: Subtotal "average" with criteria from another column

    @63falcondude, thanks your solution did work, but to implement it to my VBA is not working....
    This is what I get when running "record macro", but when I put it into my VBA it ignores it...! For testing I put another value in the cell prior to the code line of the array and it does not overwrite it, seems just to ignore this part of the code and give no error.... what can cause this??

    Please Login or Register  to view this content.
    Last edited by soreno; 08-27-2018 at 03:46 PM.

  11. #11
    Registered User
    Join Date
    03-08-2010
    Location
    Sweden
    MS-Off Ver
    MS Office 365 Mac and Windows
    Posts
    81

    Re: Subtotal "average" with criteria from another column

    After a bit of testing and tweaking, I finally made it work, was some error from my side @63falcondude array suggestion did work in my VBA solution, many thanks
    This is the correct array... case solved
    Please Login or Register  to view this content.

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,872

    Re: Subtotal "average" with criteria from another column

    Happy to help. 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. Vlookup with two column criteria, "Date" and "signed"
    By CBailey852 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-15-2018, 06:29 AM
  2. Replies: 2
    Last Post: 07-24-2017, 02:19 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. [SOLVED] How can I copy "=Average(A1:A2)" and paste as "=Average(A3:A4)" in next space in column?
    By matt_m_is_me in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-27-2014, 11:02 AM
  5. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  6. [SOLVED] Subtotal cells in column that ="value" if another column in same row ="value"
    By e89austin in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-30-2013, 09:09 AM
  7. Replies: 0
    Last Post: 07-09-2009, 04:07 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