+ Reply to Thread
Results 1 to 3 of 3

Pivot table to show average no. orders on a day of the week

  1. #1
    Registered User
    Join Date
    01-19-2017
    Location
    Birmingham, England
    MS-Off Ver
    2013
    Posts
    10

    Pivot table to show average no. orders on a day of the week

    Hi, I have a big orders spreadsheet with numerous columns, including order date in dd/mm/yy format, covering the entire year's worth of orders. I want to create a report to show on average how many orders we receive on particular days of the week. What I'm trying to identify are the busiest day(s) and the quieter day(s) based on Mon-Fri, with a view to using that as part of a case for additional resource. How can I run a pivot table which sees the order dates as Mon, Tue, Wed, etc., adds up all the Mondays, Tuesdays, etc. from the year, and shows an average number of daily orders (a count of entries) for each day? So eventually I would like to see a bar graph showing only 5 days of the week along the x axis and the average number of orders in the y axis.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Pivot table to show average no. orders on a day of the week

    You will probably have to use a helper column in the source data =TEXT(Date,"ddd") and group by that. Conversely you could also use an array formula against the raw table data.

    If this doesn't make sense, attach a workbook with sample data and I can show you what I mean.

    To Attach a File:

    1. Scroll down to the window below your post Additional Options
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Pivot table to show average no. orders on a day of the week

    Hello,

    in your source data, add a column that calculates the weekday for each item. You can have the weekday show as a number

    =weekday(A1)

    or as text

    =text(A1,"ddd")

    Now you can drag that weekday field into the pivot chart Axis (categories) area and set the value to average.

    cheers, teylyn

+ 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. Average sales per week per sales representative in pivot table
    By R12345 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-15-2016, 07:16 AM
  2. Show Group Average on Filtered Pivot Table
    By rryanp in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-06-2015, 01:40 PM
  3. [SOLVED] Pivot Table - how to show Average Mvt Time
    By stemelliott in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-20-2015, 01:54 PM
  4. [SOLVED] Using sum product to show average number of days for closed work orders
    By mmccra2858 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-01-2014, 06:42 AM
  5. Pivot Table from MS Query to Calculate Orders
    By mars242 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-07-2013, 04:59 AM
  6. Calculate orders past agreed dates in pivot table and averages in pivot tables
    By applesandpears in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 10-18-2012, 05:26 PM
  7. Replies: 5
    Last Post: 02-09-2012, 12:05 PM

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