Excel - AVERAGE Function Problem - Expert Solution

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
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc