Am using Microsoft Windows 365, Microsoft® Excel® for Microsoft 365 MSO (Version 2506 Build 16.0.18925.20076) 64-bit
Am working on building a shipping tracking sheet.
Goal:
To find the average ‘TT’ Travel Time for the past 30 days for each ‘Carrier’, for each Traveling Route (i.e.: China to NY, China to LAX, Nhava Sheva to NY, Hamburg to Boston, etc).
Looking for any pointers on how next to proceed to get my necessary results.
Have been trying to look at different functions to see what I could use, but can’t spot it yet.
Sample test sheet attached as well.
So far my function is giving correct results up to this point.
Here is the current working function:
=TAKE(SORT(CHOOSECOLS(FILTER(A3:F34,(A3:A34=”China”)*(B3:B34=”NY”),”Not Found”),1,2,5,3,4),{3,4}),-30)
Note: The ‘Filter Out’ column is only in there to make sure my function is working correctly with ‘choosecols’.
Current Problem,
Take the results from current function spill and apply the next step to get these results:
Looking to take the last 30 shipments, by date, for each of the different ‘Carriers’ and get the average ‘TT’ Travel Time for all of them for that carrier.
I can get the function to give me the ‘TT’s for each shipment in the ‘spill’, but can’t figure out how to apply it against each different ‘Carrier’ first. Then give the average ‘TT’s for the last 30 days.
Hope this isn’t too confusing.
Any points are appreciated.
