# Subtotal "average" with criteria from another column

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

2. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.``

11. ## 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. ## Re: Subtotal "average" with criteria from another column

Happy to help. Thanks for the rep!

#### Thread Information

##### Users Browsing this Thread

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

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