Finding a Daily Average

In the attached dataset, I am trying to find the most efficient way to get both average weight and volume per location per day. Is there an easier way than doing multiple levels of the "Subtotal" function?

The end result should be:

Location A: | xxx lbs/day | xxx pieces/day
Location B: | xxx lbs/day | xxx pieces/day

Thanks so much!

Seaplane Jack

ExcelForum Avg per Day.xlsx

Hello
In your example file,try:

Please Login or Register  to view this content.

Copied across from H9 and down to I10.

DBY

I'm not sure that's working exactly as I was expecting. For example, if we look at only weight for Jan 3rd and 4th for Location A (the first 5 rows of data), we get:

Total weight for Location A for Jan 3rd = 120.6
Total weight for Location A for Jan 4th = 533.0
Average weight per day for these two days is then 326.8.

I may not have explained myself correctly in my first post. Does this make sense?

Thanks, DBY!

Hi
On your example file you didn't specify any particular date to average, just: Location; Avg Wt/Day; Avg Pcs/Day. Where do you want to stipulate the date to average?

DBY,

I need to get the totals (weight & pieces) for each day for each Location and then divide that by the total number of shipping days included for each Location. The full dataset includes 300+ Locations and over 100k rows of data (records) from varying dates. For a given date there could be anywhere from one to several records.

Thanks, again...

SJ

A simpler way to say it may be: I need to total all of the weights for a given Location, then divide that by the number of unique shipping dates for that Location. I'm currently sorting by dealer and then by date, then nesting subtotals to get the sum of both pieces and weight at each change in Ship Date, and then running a count on the number of Ship Dates. It's working, but very cumbersome as I have to copy subtotals to several different worksheets to get to the end result.

~SJ

I think I see more clearly. It would appear you also need a unique count of days for each Location to divide the totals by. Have you considered using Pivot tables?

You can use formulas to get a unique count but with 100k rows of data the Pivot option might be best. I''ll upload an example of a pivot from your file for you to consider. But in the meantime you might get some other suggestions from forum members.

DBY

My take on this is different to oeldere's. The 'Pivot Summary' sheet shows the example you quoted for the 3rd and 4th of Jan filtered.

DBY