Question description:
This user has given permission to use the problem statement for this
blog.
How to calculate the average number of customers during different time periods
Solved by S. J. in 17 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/10/2018 - 05:22
Hello and thank you for choosing ExcelChat! I can help you with your problem.
Excelchat Expert
12/10/2018 - 05:22
Before we get started, this is a reminder that our policy is 1 problem per session. Let me ask you a couple of quick questions to make sure I fully understand your problem. :)
Excelchat Expert
12/10/2018 - 05:23
I believe you need help computing for the average of values within a predefined time range. May you please share the file you're working on so we could provide a solution directly to your concern. OR... you can provide a sample data set in the document preview for us to work on.
User
12/10/2018 - 05:23
Does this work
Excelchat Expert
12/10/2018 - 05:24
Yes, this would be great! Can you elaborate further on how we should go about with the computation?
User
12/10/2018 - 05:25
I'm trying to calculate the avg # of customers from 7:30-9:30
User
12/10/2018 - 05:25
Then also 11:30-1:30
Excelchat Expert
12/10/2018 - 05:26
Okay I see. Where can we see the no. of customers per time range?
User
12/10/2018 - 05:26
I believe it is shown as the customer Id
Excelchat Expert
12/10/2018 - 05:28
Oh, that seems weird. I'm not sure though if what you're trying to look for is an "average". Average would be like the "sum of things" over the "count of things". Customer ID I believe would be a unique identifier, correct?
Excelchat Expert
12/10/2018 - 05:29
So I'm not sure what we're trying to "sum" in this regard. We could try "counting" though how many customers are there per time of purchase.
User
12/10/2018 - 05:30
My question clearly states Average number of customers between 7:30 and 9:30 a.m.
Excelchat Expert
12/10/2018 - 05:31
Ohhh. Okay, let me restate it if I understand it correctly. So what exactly you're trying to compute for is the average no. of customers within each time range, considering the "from" and "to" times of purchase.
User
12/10/2018 - 05:31
Yes
Excelchat Expert
12/10/2018 - 05:32
Nice to know! Let me work on this within the document preview. I may be able to do this within 10 minutes. Let me work on it for a while and update you the soonest! :)
User
12/10/2018 - 05:32
Ok
Excelchat Expert
12/10/2018 - 05:35
Please check the answer in column I.
User
12/10/2018 - 05:36
So that would calculate the average?
Excelchat Expert
12/10/2018 - 05:36
That is correct. Let me just provide the logic behind this.
Excelchat Expert
12/10/2018 - 05:37
Basically, what we made use in here are the COUNTIF and ROUNDDOWN functions. COUNTIF allows us to sum the total no. of customers per time range. These are then divided by 2 signified by the hours duration per time range. However, there are times that the average would return a decimal value. In this regard, we make use of ROUNDDOWN to get the nearest lower integer to this decimal value.
User
12/10/2018 - 05:37
Ok thank you
Excelchat Expert
12/10/2018 - 05:37
The reason behind this is that there is no 0.5 person. So basically, an average no. of customers have a value of 17.5 would be come 17.
Excelchat Expert
12/10/2018 - 05:38
Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
Excelchat Expert
12/10/2018 - 05:38
Glad to be of help! Does this particularly solve your main query? :)
User
12/10/2018 - 05:38
Yes thank you
Excelchat Expert
12/10/2018 - 05:39
Nice to know! Do you have any more clarifications with the provided solution? :)
User
12/10/2018 - 05:39
No that's fine thank you
Excelchat Expert
12/10/2018 - 05:39
Nice! You may now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
