Solved

counting numbers by day of week in  a calculated field

Posted on 2014-09-26
7
133 Views
Last Modified: 2014-09-30
Hi there, I am running into a problem with calculated fields in regard to day of the week.  So, I ran data which gave me counts of something arbitrary, let's call them widget purchases.   So I was trying to find out how many were purchased, on avg, by hour.  So this was an easy calculation, because there were 91 days in Q2, 2014, I just took the total widgets sold by hour and divided it by 91.  That was the easy part.  

The next step was to do the same comparison using day of the week.  In this case, I had the day of week in my rows, and then a calculated field which told me the avg. amount sold by day of the week.  I got lucky because there were 91 days in the quarters and amazingly enough, there were 13 of each day of the week.  So my calculation was just total told on all Mondays, divided by 13 because there were 13 Mondays.  

My problem is what happens next quarter when there aren't an identical number of days for each day of week.  So the pivot would look something like this.
            Total Sold     Avg. Daily Sold (Calculated field)
Mon          100               = total sold for Monday/13  because there are 13 Mondays in Q2, 2014.
Tue            102               = total sold for Tuesday/13
Wed           124                      "          "          "
Thu            118                      "          "          "
Fri               116                      "          "          "
Sat              94                        "          "          "
Sun             82                       "          "          "

So, how can I create a calculated field based on the # of those days in that time period.  Any thoughts?
0
Comment
Question by:akatz66
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 40346886
Depending on how your data is collated to produce the Pivot information, Chip Pearson provides an array formula that can determine the numbers of Mondays (Tuesdays, Wednesdays, etc) between two dates:

[ http://www.cpearson.com/excel/datetimews.htm ]
---
Number Of Mondays In Period

If you need to return the number of Monday's (or any other day) that occur within an interval between two dates, use the following Array Formula:

=SUM(IF(WEEKDAY(A2-1+ROW(INDIRECT("1:"&TRUNC(B2-A2)+1)))=C2,1,0))

This formula assumes the following:
A2 contains the beginning date of the interval
B2 contains the ending date of the interval
C2 contains the day-of-week number (1=Sunday, 2=Monday,...,7=Saturday)
---
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40346893
If you have every day in a separate row, then you can change the SUM in a PivotTable to an AVERAGE. See http://www.dummies.com/how-to/content/how-to-modify-the-pivot-tables-summary-function-in.html for an example.

I hope this answers your question. If not, please elaborate.
0
 

Author Comment

by:akatz66
ID: 40346921
I think I do need to elaborate, I apologize.  There are several rows for each date.  So let's say each widget sale is a row in the data, but there will obviously be several rows of data per date.   So when I pivot it, I can easily get total counts for each day of the week.  On my data sheet, I just use a =text(a2,"DDD") then use that column as the row on my pivot table.  Then I just sum up this counter column to tell me the total # of counts per day of week.   So it's a huge conglomeration of a massive amount of rows.  
                 
I've attached a file that shows the gist of how my data is sorted, how the pivot is set up, and the calc field calculations I need.  I apologize, I should have sent this in first.

Many Thanks,
Andy
EE-Sample-Data.xlsx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40348433
It is possible to count the unique number of dates for each day of the week shown in the data, but it involves using an array formula and it is slow to process because of the large number of rows (over 29K).

Add this array formula to H3 (using [Ctrl]+[Shift]+[Enter]) and then copy down to H9:
=SUM(IF(FREQUENCY(IFERROR(MATCH(($A$2:$A$29112 & $C$2:$C$29112),($A$2:$A$29112 & F3),0),""),IFERROR(MATCH(($A$2:$A$29112 & $C$2:C29112),($A$2:$A$29112 & F3),0),""))>0,1))

You can then divide the total shown in the PivotTable by this result, which is the number of dates for that specific day.

While this is very accurate - since it actually tests the dates/days shown in your data - it is incredibly processor-intensive.  I suggest setting up a separate table of all possible calendar dates elsewhere (showing date and day) and then running this calculation on that table instead.  In fact, with a single-date calendar listing, the formula to derive days in a period is considerably simpler and faster (you can use COUNTIFS instead).  

See the attached workbook for an example.

Regards,
-Glenn
EE-Sample-Data-mod.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40352966
Here is a calculated field solution for you.  It requires adding two columns to your original data set:
1) Day-Date - that helps identify each unique day in the range by concatenating the date associated
2) SubCount - a formula that counts the fraction of the total count that the Day-Date value equals.

Update the PivotTable range to include these new columns, then add the SubCount field to the Values section.  You should see the number of times each day value is listed in the original data set.

Add a calculated field - PerDay - using the formula =Count/SubCount.  This will show the average count per day.

Example file attached.

Regards,
-Glenn
EE-Sample-Data-mod.xlsx
0
 

Author Closing Comment

by:akatz66
ID: 40353077
Thanks Glenn, I appreciate it.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40353114
I'm glad I could find a solution for you.  Admittedly, if I hadn't seen your other question, I might not have re-visited this, but it gave me an idea of how to solve it.  

Regards,
-Glenn
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question