+ Reply to Thread
Results 1 to 139 of 139

Calculating averages of specific time slots for each day in a given month, please hep?

  1. #1
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Calculating averages of specific time slots for each day in a given month, please hep?

    I have this problem where I am trying to find daily averages of people that check in specific time slots for the month of January , for example

    Monday
    8:00- 8:15 checkin: 2 need the average for this 12/1/2015-2/1/2015
    8:15-8:30 checkin:3 need the average for this 12/1/2015-2/1/2015
    8:30-8:45 checkin:2 need the average for this 12/1/2015-2/1/2015

    Tuesday
    8:00- 8:15 checkin: 2 need the average for this 12/1/2015-2/1/2015
    8:15-8:30 checkin:3 need the average for this 12/1/2015-2/1/2015
    8:30-8:45 checkin:2 need the average for this 12/1/2015-2/1/2015

    ....

    3:00-3:15 checkin: 2

    Wednesday
    etc
    etc

    And so on. .

    I will find the averages for each day for each time slot but how can I do this with 4,000 rows of data? the date span 1/1/2015-2/1/2015

    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    What needs to be averaged?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I took a stab at trying to interpret what you need. It is confusing because on your sheet, "Time Data" has a column for Daily Averages, but if it was truly to be a daily average, wouldn't you want a separate column that has the dates you are looking to average over?

    I interpreted your problem as you have the time range in column B and C on the Time Data sheet, and you want to count how many occurrences in column D of the Aux worksheet falls between those ranges (when the days match as well). From here, you can then make a Daily Average, which I have shown for you as an example on the Time Data sheet in cells H2:I2.

    Hope this helps!
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Date Time Interval Registrations
    1/1/2015 6:00 6:15 0
    1/1/2015 6:15 6:30 0
    1/1/2015 6:30 6:45 1
    1/1/2015 6:45 7:00 0
    1/1/2015 7:00 7:15 2
    1/1/2015 7:15 7:30 1
    1/1/2015 7:30 7:45 0
    1/1/2015 7:45 8:00 2
    1/1/2015 8:00 8:15 0
    1/1/2015 8:15 8:30 2
    1/1/2015 8:30 8:45 0
    1/1/2015 8:45 9:00 2
    1/1/2015 9:00 9:15 0
    1/1/2015 9:15 9:30 0
    1/1/2015 9:30 9:45 2
    1/1/2015 9:45 10:00 0
    1/1/2015 10:00 10:15 2
    1/1/2015 10:15 10:30 0


    I am trying to average the registrations for those specific time slots by day so example the month of January on Monday 1/1/2015 averages of overall data, Tuesday overall averages 1/2/2015. . and so on I am trying to build a model that can do this for 4000 rows of data for the times slots.

  5. #5
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Mcmahobt:


    This does help! Thank you, I have never used SUMPRODUCT before can you explain this to me? and the logic what you did there?

    I am pretty beginner at excel

  6. #6
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    SUMPRODUCT is preferable here rather than SUMIF or COUNTIF because of how your data is setup. For example, column A in your Aux sheet has date and time values, while your date and time values are split into columns A and B in the Time Data tab.

    Because of this, I wanted to use INT() which rounds down numbers. I wanted to apply this to the entire A column in Aux so that you could compare those dates to the dates within column A in Time Data.

    However, INT() does not like array objects. However, used within SUMPRODUCT, this can be done. This is the main reason SUMPRODUCT was used.

    For the actual use of SUMPRODUCT itself, its intent is to return the sum of multiple array/ranges products. However, if you are using TRUE/FALSE logic for a range (read as Binary), the values will either be 0 or 1.

    This, multiplied by the number of occurrences set by the end of the SUMPRODUCT formula which dictates your time ranges, merely counts the number of occurrences that fit the criteria.

    This is probably a very long-winded explanation, but I have found SUMPRODUCT to be one of my favorite workaround functions in Excel.

  7. #7
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Very nice explaination, thank you for that. The AUX sheet contains specific times a guest enters into the club so instead I wanted to group these amounts into the time periods for example 8:00-8:15 would have 2 total, this is why I used the SUMIF function so you think SUMPRODUCT would work better after grouping the times into the time intervals(ex 8:00-8:15) ?

    also after 6 blocks I have to minus one guest I have to minus one guest since that person is leaving the lounge I am trying to graph data that . . after that calculation I would like to find the daily averages for those specific time intervals when the guests are their at the same time so then I can understand the capacity and if I need to increase the size of the club/store

  8. #8
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Very nice explaination, thank you for that. The AUX sheet contains specific times a guest enters into the club so instead I wanted to group these amounts into the time periods for example 8:00-8:15 would have 2 total, this is why I used the SUMIF function so you think SUMPRODUCT would work better after grouping the times into the time intervals(ex 8:00-8:15) ?

    also after 6 blocks I have to minus one guest I have to minus one guest since that person is leaving the lounge I am trying to graph data that . . after that calculation I would like to find the daily averages for those specific time intervals when the guests are their at the same time so then I can understand the capacity and if I need to increase the size of the club/store

  9. #9
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    By subtract one guest...do you mean one guest from the sum of the rest? Or subtract one from that specific time slot?

  10. #10
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Assuming 1 guest stays for a full 90 mins in the time intervals you would subtract 1 person every 90 mins ~6 blocks

    Example:
    Register At same time
    8:00 8:15 2 2
    8:15 8:30 1 3
    8:30 8:45 5 8
    8:45 9:00 10 18
    9:00 9:15 5 23
    9:15 9:30 3 [26]
    --- - - - -- - - - - - - - - - - - - - - - - - - -- - - -
    9:30 9:45 2 [26]
    9:45 10:00 5 28

  11. #11
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Assuming 1 guest stays for a full 90 mins in the time intervals you would subtract 1 person every 90 mins ~6 blocks

    Example:
    Register At same time Guests
    8:00 8:15 2 2
    8:15 8:30 1 3
    8:30 8:45 5 8
    8:45 9:00 10 18
    9:00 9:15 5 23
    9:15 9:30 3 [26]
    --- - - - -- - - - - - - - - - - - - - - - - - - -- - - - Right here is when 1 guest leaves and repeats every 6 blocks
    9:30 9:45 2 [26]
    9:45 10:00 5 28

  12. #12
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Assuming 1 guest stays for a full 90 mins in the time intervals you would subtract 1 person every 90 mins ~6 blocks

    Example:
    Register At same time Guests
    8:00 8:15 2 2
    8:15 8:30 1 3
    8:30 8:45 5 8
    8:45 9:00 10 18
    9:00 9:15 5 23
    9:15 9:30 3 [26]
    --- - - - -- - - - - - - - - - - - - - - - - - - -- - - - Right here is when 1 guest leaves and repeats every 6 blocks
    9:30 9:45 2 [26]
    9:45 10:00 5 28

  13. #13
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I'm still not following. What is the calculation for "Clients at the Same Time"?

  14. #14
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Also, this will be the formula you want to use in column D within the Time Data sheet to count the entries:

    Please Login or Register  to view this content.

  15. #15
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Thank you.

    Clients at same time would be clients that are in the room at the same time so the amount adds up from that column as you can see.

    :00 8:15 2 2
    8:15 8:30 1 3=(2+1)
    8:30 8:45 5 8=(5+3)
    8:45 9:00 10 18=(10+8)
    9:00 9:15 5 23
    9:15 9:30 3 [26]
    --- - - - -- - - - - - - - - - - - - - - - - - - -- - - - Right here is when 1 guest leaves and repeats every 6 blocks
    9:30 9:45 2 [26]
    9:45 10:00 5 28

  16. #16
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Thank you.

    Clients at same time would be clients that are in the room at the same time so the amount adds up from that column as you can see.

    :00 8:15 2 2
    8:15 8:30 1 3=(2+1)
    8:30 8:45 5 8=(5+3)
    8:45 9:00 10 18=(10+8)
    9:00 9:15 5 23
    9:15 9:30 3 [26]
    --- - - - -- - - - - - - - - - - - - - - - - - - -- - - - Right here is when 1 guest leaves and repeats every 6 blocks
    9:30 9:45 2 [26]
    9:45 10:00 5 28

  17. #17
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    See if the attached workbook solves your problem.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Thanks so much this will help out. . .btw do you offer private skype tutoring? I can compensate you for your help, I am trying to grow my excel skills and I am in a intermdiate operations job which requires heavy excel work

  19. #19
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I personally do not offer tutoring...most of my Excel knowledge I have gained through this forum or on the job. I also do not know enough VBA to feel adequate tutoring others. There are several users that link to their commercial services within their signature for tutoring, maybe try viewing the top Members List users for additional info.

    As far as growing Excel skills, I have found that posting problems on here and trying to fully understand how they work is one of the better ways of growing your Excel knowledge.

    Hope this helps!

  20. #20
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Thanks and your right I am learning alot right now, I have encountered a new problem I made a new excel sheet with the capacity model.

    Attached you will find the capacity model. I created a “Home” sheet to store some data to be use in the future. “Time Data” is now getting the information from the “ARS Data” and using the data from the “Home” sheet to create the averages.

    Now, we should be able to create the graphics based on “Time Data”. Also, we have to find a way to populate “Aux” column A and B automatically from the “ARS Data” and drag the formulas in C and D.

    Can you please help me with this problem?

    Thanks
    Attached Files Attached Files

  21. #21
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Can you rephrase exactly what you need? What data do you need populated from where? And this is the first mention of "graphics" that I can recall, what does that refer to?

    EDIT: Took a stab at what you may have meant. The only part that may be off are the averages for the Time Data sheet...why were the original references looking at Total Days on the Home sheet? Don't you just want an average based on time values?

    Hope this helps!
    Attached Files Attached Files
    Last edited by mcmahobt; 02-05-2015 at 11:49 AM.

  22. #22
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I had to change the entire excel file, this is the correct layout my team wants. To summarize my purpose of this is to make the user able to generate the whole analysis by just copying new data for any club/room.

    So if a user wants to populate modified data, we want the “Aux” sheet to populate automatically using the "ARS" sheet, specific columns would be column A (Datecreation), B(TotalCount), and drag the formulas in C and D.

    Does that make more sense?

  23. #23
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    what you gave me makes sense, but for columns C and D do I have to do anything ?

    I have also attached what I did would this work too? onky difference is that Column B I just linked them but you have ranges for Column B in AUX
    Attached Files Attached Files

  24. #24
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Columns C and D of which sheet?

    Also, in the initial sheet you posted, column B in Aux did not have any links, just raw data. That's why I linked it to the ARS Data sheet, so that if a user pastes data, Aux will autopopulate.
    Last edited by mcmahobt; 02-05-2015 at 12:45 PM.

  25. #25
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Columns C and D of AUX sheet.

    Yes what you did was correct. I am just trying to figure out how to automate the C and D columns of AUX sheet to ARS sheet

  26. #26
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    What do you mean by automate C and D columns in Aux sheet to the ARS sheet?

    If I'm not misunderstanding you, column C of Aux is reliant on column A of Aux (which is in turn pulled from the ARS sheet). Column D is reliant on the Home sheet, but you don't really need to reference the home sheet at all. You could put this into D2 and drag down to achieve the same result:

    Please Login or Register  to view this content.
    That means that both columns C and D are now reliant on column A data, which is pulled directly from ARS data.

  27. #27
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Perfect then everything looks good here, thanks for the help I'll reach out if I need some more help. But as far as the data goes it looks good, the C and D columns dont have to be changed then.

  28. #28
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi Mcmahobt,

    With the excel sheet we created my supervisor informed me this, I guess he wants some changes made.

    "The problem here is that if the ARS data is longer, the link won’t work. I know we can do a copy and paste of the whole column with excel macros, but not sure if there is a formula that can do something similar. Can you work on this?

    Column C and D are calculated based on column A and some Home values. This formulas have to applied to all the rows of Column A and B.
    "

    Thanks Please get back to me soon.

  29. #29
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi Mcmahobt did you recieve my message?

  30. #30
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    This can be done through both VBA and formulas, the latter being more processing-intensive most likely. I'm about to head out for the day though so unfortunately I will not be able to provide a robust example for now.

  31. #31
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I understand we can discuss more when you get free again, thanks for help talk to you tomorrow

  32. #32
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    pigment,

    Do you prefer a macro, or formulaic method of updating the data values in the Aux sheet?

  33. #33
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I figured out the sheet but I need help with another new sheet, these are the requirments I am struggling with now, dont worry about the last sheet.

    - A new field in the “Home” for the current seats capacity
    - Create a minimum and maximum column for the “Same Time” section in the “Time Data” sheet
    - Graph the daily averages (M,T,W,T,F,S,S), maximum, minimum and current capacity
    - Dragging the formulas in column C and D of “Aux” sheet with the macro
    Attached Files Attached Files

  34. #34
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    These are the 4 things I need help on with the new excel sheet I attached

  35. #35
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    A few questions about your requirements:

    1) What is current seats capacity? What is the calculation for that?
    2) Minimum/Maximum for what combination? It would either be maximum day of the week values by time, or maximum time values by day of the week.

  36. #36
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    1) I am checking on this

    2) its going to be for each day for the "same time" section

  37. #37
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    The problem for finding a minimum value by day is that since every day has a minimum value of zero (between 0000 and 0545), those values would all return zero.

  38. #38
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I checked with my collegue and

    1) the capacity equation can be any number from the "Home" sheet, this is more for the user to input and get results

    2) its going to be for each day but if there is another creative way to get more reliable information for example worst case scenrio of most of the people then we can also work with that

  39. #39
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    We can take the max/min by day, if you would like to just exclude values of zero?

    I'm also not following your post about the capacity equation. Any number from the Home sheet?
    Last edited by mcmahobt; 02-06-2015 at 01:15 PM.

  40. #40
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Ok lets do that, I think for now we will keep the zeros if not later I can remove them.

    For the capacity I just made a field "Current seat capacity" and selected =D11. It can be any number even =D12 (this is in the Home sheet) This way future users can manipulate data and get the results they need.

  41. #41
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Does current capacity change according to day of the week? If you are trying to graph average, min, and max by day, but also graph current capacity, you'll need to break that out by day of the week as well.

  42. #42
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Yes you are correct, everything will be broken down to day of the week. Logically we want this model to make inference of capacity and size planning for a room.

  43. #43
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Yes you are correct, everything will be broken down to day of the week. Logically we want this model to make inference of capacity and size planning for a room.

  44. #44
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi Mcmahobt. thanks for your continued help again, were you able to produce something?

  45. #45
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Yes, I apologize - I got caught up with something at work. See attached for max, min, and average broken out by date for your data, along with a corresponding graph (on its separate tab). I assumed you were able to work out the capacity size problem yourself.
    Attached Files Attached Files

  46. #46
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    No problem and Yes it looks great! I hope one day I can get as good with your excel skills

    So can you explain this requirement and how you did it? my collegue says to do it with macro recording

    - Dragging the formulas in column C and D of “Aux” sheet with the macro -


    Capacity I figured out

  47. #47
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Also how did you post daily max, daily min, and daily avg all in one graph?

  48. #48
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    My last post got deleted not sure why.

    Anyway can you guide me how you did this step?

    - Dragging the formulas in column C and D of “Aux” sheet with the macro


    Also can you explain how you put daily max, daily min, and daily average all on one graph?

  49. #49
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Take a look at the attached workbook. Usually, I don't advocate recording macros, since a lot of steps get repeated, and it is a volatile process. I am limited in my VBA knowledge, but tried to eliminate as much extraneous script as I could without breaking the macro. I'm sure it isn't as efficient as it could be. To update the data from the ARS Data sheet to the Aux sheet, click the button I have inserted in the Aux sheet that is tied to the VBA. Hopefully this will work on your end.

    The max, min, and average graph is just a line graph that has the max, min, and averages plotted on it as separate series. There are many videos on youtube or tutorials you could find through google that go in depth as to how to create these.
    Attached Files Attached Files

  50. #50
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Awesome, this looks great. Thank you for your help and time. I'll reach out if I have any other issues.

    Thank you.

  51. #51
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    1. Incorporating the "Reg.prior Depature" to the Time or Column B in the AUX sheet, we created a macro that would essentially drag the formula you made down to whatever the user has pasted. So if the User changes the time in Reg. Prior Depature it would subtract the time from the B column in the AUX sheet.


    2. Applying the constraints of 8:00am to 5:00pm in the Operating hours section in the HOME sheet and applying this constraint to the data overall so it can affect the Volume summary calculations and square footage calculation with the constraints.

    3. Formulas for Average visits, Peak Passengers, and Minimum passengers
    Attached Files Attached Files

  52. #52
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I'm confused as to what you require besides the Average/Peak/Minimum calculations...

  53. #53
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    1. If the user selects values 60, 75 etc in the HOME sheet I want this to subtract that value in the TIME column in the AUX sheet. I think a TIME function would work best here but I am having trouble setting it up.

    2. For the Operating Hours in the HOME sheet, I want to set up a constraint of when the lounge will be open for example 8:00 to 5:00 pm this would exclude data from being presented in the graph and on the data sheets that are past 5:00pm and before 8:00 am some function is needed for this.

    3. After those are set up I just want to get the values based on those parameters discussed. (average, max, min)

  54. #54
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    1. If the user selects values 60, 75 etc in the HOME sheet I want this to subtract that value in the TIME column in the AUX sheet. I think a TIME function would work best here but I am having trouble setting it up.

    2. For the Operating Hours in the HOME sheet, I want to set up a constraint of when the lounge will be open for example 8:00 to 5:00 pm this would exclude data from being presented in the graph and on the data sheets that are past 5:00pm and before 8:00 am some function is needed for this.

    3. After those are set up I just want to get the values based on those parameters discussed. (average, max, min)

  55. #55
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Why set open and closed hours...why not just hide the data on ALD Updated that is before/after the open close hours? This would automatically alter the range of the chart on Graph-Passengers. Also, for your Time calculation, try this and drag down:

    Please Login or Register  to view this content.

  56. #56
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Ok I think I figured out the first requirement, tell me what you think and about the others

    =B2-TIME(0,Home!$G$6,0)

  57. #57
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    "Why set open and closed hours...why not just hide the data on ALD Updated that is before/after the open close hours? This would automatically alter the range of the chart on Graph-Passengers. Also, for your Time calculation, try this and drag down:"

    Essentially because the user will be changing these values and also the user willl be pasting data into the "ALD Data-Input" tab as raw data and the times might be different. My boss wants a constraint formula set up for that will affect the data.

  58. #58
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    And what you did works for the first requirement!! thanks!

  59. #59
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    So were able to figure out how to finish the 2nd requirement?

  60. #60
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I was just speaking to my boss and the best to do it is the SUMIF function. . . this will affect the calculations of the average,maximim, and minimum.

  61. #61
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    The second requirement is pretty time intensive. Look into creating dynamic ranges based for charts on google and play around with that.

  62. #62
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by pigment01 View Post
    I was just speaking to my boss and the best to do it is the SUMIF function. . . this will affect the calculations of the average,maximim, and minimum.
    SUMIF? For what?

  63. #63
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    So in the "Operating Hours" section in the HOME sheet we are going to have 2 cells--->which I have shown in the attached file.

    and we want use the existing formula:


    =SUM(C3:INDEX(C:C,MAX(ROW($A$3),COUNT(C$3:C3)-Home!$G$3/15+ROW($A$3))))

    to apply the condtions the user will set. My boss was saying for the maximum the formula could look like this

    =MAX(C3:INDEX(C:C,MAX(ROW($A$3),COUNT(C$3:C3)-Home!$G$3/15+ROW($A$3))))

    We dont need the minimum just the Average(with the time parameters the user wants) and Maximum( with the time parameters the user wants) which the maximum will be automatically used to calculate the sqaure footage value.

    Is this possible you can help me?
    Attached Files Attached Files

  64. #64
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Your ALD Updated sheet is broken out by days of the week. There is no indication which day of the week you want the average, peak, and minimum values for. Also, I think this has been asked before, but why not just use AVERAGEIFS() to average your data? Why the complex formula?

  65. #65
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Ya I think I am confusing myself that function you mentioned migth work better. We are getting the daily average we will be using the (Number of check ins) to get the total then the average

    and for the max we will be getting the entire block of "passengers at the lounge by time" exlcuding the total in the that block.

    If you think the AverageIF function works then maybe we can do that. Please let me know.

    We dont need to get the minimum.

  66. #66
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Remember we need to incorporate those "Operating Hours" into the picture the user will be able to enter desired operating hours time and affect the average visits and peak passengers calculations.

  67. #67
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    For average visits, try this:

    Please Login or Register  to view this content.
    and for peak passengers during operating hours:

    Please Login or Register  to view this content.
    The second formula needs to be entered with CSE. However, array formulas don't like to work with merged cells. So first, unmerge c4:D4, enter in the above formula as a CSE formula, then re-merge the two. It returned a value of 295 for me.

  68. #68
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi thanks for getting back to me this is what I am getting when I enter in the averageIF formula, and for the maximum It does'nt let me enter it in. I have attached the updated file.

    Notice I unmerged the cells for Operations Hours so the user can enter what they want for operating hours.

    Can you see whats wrong here?
    Attached Files Attached Files

  69. #69
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I think we almost got it!

  70. #70
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi Mcmahbot,

    Were you able to see what the problem here was when I entering your formulas?

    I attached the excel file to show you.

  71. #71
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    pigment,

    See attached workbook for possible solution. The reason you were returning errors with the above formulas is because the attached workbook from your previous post is missing a column that I had used for both calculations. I have included the column in this current workbook, column J within ALD Updated. If that data is incorrect, we'll need to go back to the drawing board and understand where exactly you want to pull information from.
    Attached Files Attached Files

  72. #72
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi Mcmahobt,

    Thanks for the help, I was able to use what you did before to get the values I wanted. I need your help with one final thing now,

    I just checked with my boss, and the total column will not exist. We will be calculating the average from the entire "Passengers At the Lounge By Time".

    If we were to calculate that entire block it gives us the weekly avaergae, and then from there we can calcuate the yearly average. Can you recreate the Average formula with this criteria and get back to me please?

    The peak passengers works good here.

    I have attached an updated excel sheet. Please get back to me soon I would really appreciate it.

    Thank you.
    Attached Files Attached Files

  73. #73
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    If you want to average all the weekdays at once, you won't be able to use AVERAGEIFS since the data ranges you compare it to must be the same dimension. A workaround for this is a nested IF statement inside of an AVERAGE function, entered as an array with CSE. Try this in C3 on the Home tab:

    Please Login or Register  to view this content.
    and again, confirm with CSE.

  74. #74
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Thanks! what is CSE?

    I am going to try the formula now.

  75. #75
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I just pasted the formula into the Home sheet for Average visits and returned a value of 0 for me, I am not sure what is wrong?

    Also are you using the data range for "Passengers at the lounge by time" we will only be using that block for the calculation.

  76. #76
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Ohhh! I see now you do Ctrl+Shift+Enter and it gave me 34.082

    so now I can get yearly by *72 and also monthly by dividing the yearly/12.

    Also this formula you created can you explain it to me more in detail for my understanding? I am also going to explain this to my boss.

    Thanks!

  77. #77
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    CSE means Ctrl + Shift + Enter. This is how you enable an array formula. Press the keys (while holding each down in the process) and when done correctly, you should see a set of { } around the formula to let you know that an array formula has been used.

    And yes, I used the entire data range for Passengers At the Lounge by Time within the ALD Updated Worksheet.

  78. #78
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    The formula which you have provided works great, but I made a mistake we want the data for "Number of Check Ins" because the data on the right is accumlated and it wouldnt be useful.

    I apolgize for that can you see how I can make that modification in the formula so its using the block of data "Number of Check Ins" excluding the Total?

  79. #79
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    would this work?

    using CSE

    =AVERAGE(IF(('ALD Updated'!$A$3:$A$98>=Home!$F$9)*('ALD Updated'!$A$3:$A$98<Home!$G$9),'ALD Updated'!$C$3:$I$98))

  80. #80
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hello Mcmahobt,

    I have completed the excel sheet we were working on, but I need help with a few minor tweaks. Do you have some experience with recording Macros?

  81. #81
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    With recording macros, yes...but my VBA knowledge is limited.

  82. #82
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    With recording macros, yes...but my VBA knowledge is limited.
    Ok cool

    I will try to figure out on my own for the recorded macros part.

    Regarding the minor tweaks I am having some difficulty, since the lounge closes at 5:00PM I do not want the formulas in hte volume summary to take in to effect the 5:00PM so would I just remove the equal to sign in the formula? and where in the formula would I do this?

    My boss suggested this, but I do not where to remove the equal to sign and just keep it less than.
    Attached Files Attached Files

  83. #83
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Which formula are you talking about? Specifically, which cell and sheet.

  84. #84
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Which formula are you talking about? Specifically, which cell and sheet.
    Sorry, the sheet is "HOME" and the formula is Estimated Weekly Visits Cell C3

  85. #85
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Which formula are you talking about? Specifically, which cell and sheet.

    I made the tweak in the formula and removed the equal sign would this logically make sense?

    =SUMIFS('ALD Updated'!$B$3:$B$98,'ALD Updated'!$A$3:$A$98,">="&Home!F7,'ALD Updated'!$A$3:$A$98,"<"&Home!G7)

  86. #86
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Try this instead:

    Please Login or Register  to view this content.

  87. #87
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Perfect! Thanks Mcmahobt

  88. #88
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hello,

    I am testing my model I created and when I am pasting new data to be analyzed in the ALD Data-Input sheet and then I run the recorded macro Ctrl+R, I am returning values however in the "AUX" sheet you can see that in column B I am not returning some values. I am not sure what is wrong here can you please help me with this so when I paste new data I can get the correct information back?

    *After test data has been pasted The first row in ALD input sheet needs to be deleted for the macro to run. hit Ctrl+R to run the recorded macro and you can see the values I am returning for that time column in the AUX sheet.**

    Thanks,

    I have attached the model and 2 test data excel files that will be inputted into the ALD data-Input Tab.
    Attached Files Attached Files

  89. #89
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Is F6 on the Home sheet meant to be in minutes? As in 105 minutes?

  90. #90
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Is F6 on the Home sheet meant to be in minutes? As in 105 minutes?
    Thats correct I also figured out what the issue is but I do not know how to make this formula, pretty much when the macro is runned the "Reg. Prior Depature" time which is in minutes in the HOME sheet subtracts the time value in the AUX sheet and when it does that for time data that is 0:00 the result is a negative value, so my boss said is to make a IF statement in the formula where if it gets negative subtract 24:00 to bring the time value to correct value.

    Does that make sense?

  91. #91
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    For row deletion issue I am having, I will tell the user to paste data without the titles so the formula can automatically pick the data up

  92. #92
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Here is an example, I wont have the user paste the titles in the ALD Data-Input sheet.

    I just need to put that condition in to the formula in the B Column of the AUX sheet.

  93. #93
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Is F6 on the Home sheet meant to be in minutes? As in 105 minutes?
    Sorry here is the file with the runned macro you can see the issue in the AUX sheet and what I am talking about

  94. #94
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    A few things.

    1) The equation to put in B2 on Aux should be this; note that an IF statement is not used because it could cause problems if military time is not used:

    Please Login or Register  to view this content.
    and dragged down.

    Along with this, the formula in C2 on Aux should be this:

    Please Login or Register  to view this content.
    and dragged down/across.

  95. #95
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    A few things.

    1) The equation to put in B2 on Aux should be this; note that an IF statement is not used because it could cause problems if military time is not used:

    Please Login or Register  to view this content.

    and dragged down.

    Along with this, the formula in C2 on Aux should be this:

    Please Login or Register  to view this content.
    and dragged down/across.


    Thank you Mcmahobt that will help, also I was able to get this formula from a coworker but I do not understand the formula to provide an explaination and for my understanding can you please walk me through this formula for what we are doing?

    =IFERROR(IF('ALD Data-Input'!G1="","",TIME(LEFT('ALD Data-Input'!G1,2),RIGHT('ALD Data-Input'!G1,2),0)-TIME(0,Home!$F$6,0))+(TIME(LEFT('ALD Data-Input'!G1,2),RIGHT('ALD Data-Input'!G1,2),0)<TIME(0,Home!$F$6,0)),"N/A")

  96. #96
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    A few things.

    1) The equation to put in B2 on Aux should be this; note that an IF statement is not used because it could cause problems if military time is not used:

    Please Login or Register  to view this content.
    and dragged down.

    Along with this, the formula in C2 on Aux should be this:

    Please Login or Register  to view this content.
    and dragged down/across.
    If you think your formulas work better can you provide an explaination for those as well?

  97. #97
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Besides the fact that you keep altering your workbook, for your current formula in C2 to work correctly, you need to correctly align your data in the Airline Load sheet so that AA is matched up with 95% and 0.50% (assuming those values correlate to that). Right now it is correlating to Load Factor and Load % (values), and trying to multiply that by K1 in your ALD Data-Input sheet, which is why you are receiving an error for that calculation.

    As to my first formula, I prefer that because it is not redundant and is more concise than the one your coworker gave you. You can chose either, they both return the same results.

  98. #98
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Besides the fact that you keep altering your workbook, for your current formula in C2 to work correctly, you need to correctly align your data in the Airline Load sheet so that AA is matched up with 95% and 0.50% (assuming those values correlate to that). Right now it is correlating to Load Factor and Load % (values), and trying to multiply that by K1 in your ALD Data-Input sheet, which is why you are receiving an error for that calculation.

    As to my first formula, I prefer that because it is not redundant and is more concise than the one your coworker gave you. You can chose either, they both return the same results.

    Thanks! I will choose yours, can you please walk me through your formula for the 1st one because of my beginner skills with excel I am not able to understand the flow of your formula. I don't even know what a MOD function is, your explaination will be very helpful please


    =IFERROR(IF('ALD Data-Input'!G2="","",MOD(TIME(LEFT('ALD Data-Input'!G2,2),RIGHT('ALD Data-Input'!G2,2),0)-TIME(0,Home!$F$6,0),1)),"N/A")

  99. #99
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Besides the fact that you keep altering your workbook, for your current formula in C2 to work correctly, you need to correctly align your data in the Airline Load sheet so that AA is matched up with 95% and 0.50% (assuming those values correlate to that). Right now it is correlating to Load Factor and Load % (values), and trying to multiply that by K1 in your ALD Data-Input sheet, which is why you are receiving an error for that calculation.

    As to my first formula, I prefer that because it is not redundant and is more concise than the one your coworker gave you. You can chose either, they both return the same results.

    Thanks! I will choose yours, can you please walk me through your formula for the 1st one because of my beginner skills with excel I am not able to understand the flow of your formula. I don't even know what a MOD function is, your explaination will be very helpful please


    =IFERROR(IF('ALD Data-Input'!G2="","",MOD(TIME(LEFT('ALD Data-Input'!G2,2),RIGHT('ALD Data-Input'!G2,2),0)-TIME(0,Home!$F$6,0),1)),"N/A")

  100. #100
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Besides the fact that you keep altering your workbook, for your current formula in C2 to work correctly, you need to correctly align your data in the Airline Load sheet so that AA is matched up with 95% and 0.50% (assuming those values correlate to that). Right now it is correlating to Load Factor and Load % (values), and trying to multiply that by K1 in your ALD Data-Input sheet, which is why you are receiving an error for that calculation.

    As to my first formula, I prefer that because it is not redundant and is more concise than the one your coworker gave you. You can chose either, they both return the same results.
    I looked up the MOD function but I am not understanding its incorporation and the overall flow of it.

  101. #101
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    The formula that essentially dictates how MOD is run can be shown by Numerator-Divisor*INT(Numerator/Divisor). INT rounds a value down to the nearest integer. So since time values are nothing more than numerical ratios of a 24 hour day in Excel stored as time, we can see that the formula in B2 on Aux is computing:

    0030 (12:30 AM minutes in military time) - 105 minutes (an hour and 45 minutes). As integers, these values are 0.02083 and 0.07292 respectively. Since you are subtracting the time on the ALD Data-Input sheet from the Home sheet, the value NEEDS to be positive. Why? Because as far as we are concerned (for now at least), time cannot be negative. This means that we need to trick Excel into just subtracting that extra time from the previous night.

    So looking back at MOD, it works thanks primarily to INT(). This is because INT() always rounds down. Therefore, when you subtract 0.07292 from 0.02083, your result of -0.05209 will actually round DOWN to -1.

    So then using 1 as our denominator, we see the MOD formula calculates out to be:

    0.02083-0.07292-1*-1 =

    -0.05209--1 = -0.05209+1 = 0.94791

    In time value, that is 22:44 military time.
    Last edited by mcmahobt; 03-11-2015 at 03:54 PM.

  102. #102
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    The formula that essentially dictates how MOD is run can be shown by Numerator-Divisor*INT(Numerator/Divisor). INT rounds a value down to the nearest integer. So since time values are nothing more than numerical ratios of a 24 hour day in Excel stored as time, we can see that the formula in B2 on Aux is computing:

    0030 (12:30 AM minutes in military time) - 105 minutes (an hour and 45 minutes). As integers, these values are 0.02083 and 0.07292 respectively. Since you are subtracting the time on the ALD Data-Input sheet from the Home sheet, the value NEEDS to be positive. Why? Because as far as we are concerned (for now at least), time cannot be negative. This means that we need to trick Excel into just subtracting that extra time from the previous night.

    So looking back at MOD, it works thanks primarily to INT(). This is because INT() always rounds down. Therefore, when you subtract 0.07292 from 0.02083, your result of -0.05209 will actually round DOWN to -1.

    So then using 1 as our denominator, we see the MOD formula calculates out to be:

    0.02083-0.07292-1*-1 =

    -0.05209--1 = -0.05209+1 = 0.94791

    In time value, that is 22:44 military time.
    You are a genius its impressive to see you are able to come up with this from these situations.


    Great explaination! so the INT function is naturally part of the MOD function since it rounds down?

  103. #103
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Yes. The MOD function incorporates INT in its calculation.

  104. #104
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Yes. The MOD function incorporates INT in its calculation.
    Very nice! Much appreciated for your help, always

  105. #105
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Yes. The MOD function incorporates INT in its calculation.
    Hi Mcmahobt, I am working on a new assignment but shouldn't be as complicated as before, anyway I am just trying to group these countries by region and also sum the data up according to the regions. I have attached a file and below the conditions, I was thinking a pivot table but I have never actually used one can you help me?

    Thanks

    This is how the countries should be grouped:

    1. Mexico: All Mexican issuers.
    2.Brazil: All Brazilian Issuers.
    3. Andes: All Issuers from Colombia, Venezuela, Ecuador, Bolivia and Peru.
    4. Caribbean: All issuers from Dominican Republic, Haiti, Aruba, Barbados, Jamaica, Puerto Rico, Trinidad and Tobago, Netherlands Antilles and the Bahamas.
    5. Southern Cone: all issuers from Argentina, Uruguay, Chile and Paraguay.
    6. Central America: All issuers from Guatemala, Honduras, El Salvador, Nicaragua, Costa Rica, Belize and Panama.

  106. #106
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    You attached two files...I'm assuming you want the 15.5 KB version manipulated?

  107. #107
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    You attached two files...I'm assuming you want the 15.5 KB version manipulated?
    Both need manipulation in accordance with the conditions of the region, but if I understand how to do one I can do the other one

  108. #108
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Even though I do not like working with PTs, if you're not very well-versed with Excel, they're probably your best bet. See attached for a possibility. You'll notice a few changes to your data:

    1) I turned your data into an Excel Table. This is so as you add more records, the newly added formula in column A will automatically update.
    2) The formula in column A is pulling information from the Settings sheet. This is to automate your process - if you'd rather not have it, delete the formulas in column A, enter the locations that correspond to the countries manually, and delete the Settings worksheet.
    3) I inserted slicers to make navigating the PT easier. It will allow you to select one location at a time and view their respective totals and grand total as well.

  109. #109
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Even though I do not like working with PTs, if you're not very well-versed with Excel, they're probably your best bet. See attached for a possibility. You'll notice a few changes to your data:

    1) I turned your data into an Excel Table. This is so as you add more records, the newly added formula in column A will automatically update.
    2) The formula in column A is pulling information from the Settings sheet. This is to automate your process - if you'd rather not have it, delete the formulas in column A, enter the locations that correspond to the countries manually, and delete the Settings worksheet.
    3) I inserted slicers to make navigating the PT easier. It will allow you to select one location at a time and view their respective totals and grand total as well.
    NICE!!! this looks great thanks for your help, I should be able to do the rest of the excel files similar to this!

  110. #110
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    Even though I do not like working with PTs, if you're not very well-versed with Excel, they're probably your best bet. See attached for a possibility. You'll notice a few changes to your data:

    1) I turned your data into an Excel Table. This is so as you add more records, the newly added formula in column A will automatically update.
    2) The formula in column A is pulling information from the Settings sheet. This is to automate your process - if you'd rather not have it, delete the formulas in column A, enter the locations that correspond to the countries manually, and delete the Settings worksheet.
    3) I inserted slicers to make navigating the PT easier. It will allow you to select one location at a time and view their respective totals and grand total as well.

    Hi Mcmahobt,

    I am having a simple issue of generating random values and those random values attached to a ID. Pretty much I need 500 random numbers generated for keytype SEA_S_UniqueCOdes and SEA_A_UniqueCodes.

    The random number generation will show up in column L with the 5 digit random numbers attached to the ID SEA031915XXXXX, note the XXXXX would be the random number generated. I tried to work it out on the left but having some issues. Can you generate the 500 values for each SEA_S_UniqueCOdes and SEA_A_UniqueCodes?

    Thanks
    Attached Files Attached Files

  111. #111
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Also, in the random number generation there should not exist any duplicate number generated

  112. #112
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I need more information...can the random numbers repeat? Are all the numbers spaced out the same within the workbook you provided?

  113. #113
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    I need more information...can the random numbers repeat? Are all the numbers spaced out the same within the workbook you provided?
    Actually, I think I got it can you see if I did correctly?

    THe numbers should have no duplicates and the format should look like how it does. I used the duplicate function to remove the duplicates.


    Heres my updated sheet.
    Attached Files Attached Files

  114. #114
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I'm assuming you obtained column E by just pasting over the values from column D. If this is the case, although this may work by inspection, RAND() and RANDBETWEEN() both can repeat values. For example, both the numbers 47144 and 94115 appear twice within your ID column E on Worksheet. Take a look at the attached workbook; column F on the Worksheet tab shows where the duplicates are.
    Attached Files Attached Files

  115. #115
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    I'm assuming you obtained column E by just pasting over the values from column D. If this is the case, although this may work by inspection, RAND() and RANDBETWEEN() both can repeat values. For example, both the numbers 47144 and 94115 appear twice within your ID column E on Worksheet. Take a look at the attached workbook; column F on the Worksheet tab shows where the duplicates are.
    Gotcha this will work!! thanks for the help. I am learning a lot about excel through my new job all these functions etc

  116. #116
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by pigment01 View Post
    Gotcha this will work!! thanks for the help. I am learning a lot about excel through my new job all these functions etc
    I'm not sure how you took my previous comment, but I was telling you that there are, in fact, duplicates in your data. If your data is static then you can swap out those values that are duplicates, but if your data is dynamic I would recommend following a different method.

  117. #117
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    I'm not sure how you took my previous comment, but I was telling you that there are, in fact, duplicates in your data. If your data is static then you can swap out those values that are duplicates, but if your data is dynamic I would recommend following a different method.
    The set up you had was what I was looking for and then I also used the remove duplicate function so it was good to go!

  118. #118
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    I'm not sure how you took my previous comment, but I was telling you that there are, in fact, duplicates in your data. If your data is static then you can swap out those values that are duplicates, but if your data is dynamic I would recommend following a different method.
    I had a another question, for our Sizing Model created, my boss wants to know if it is possible to remove the extra times on the graph? for example operating hours will show data and when the lounge is closed the data will not show up.

    In Summary when we change the operating hours the graph is not updating itself here is an example of San Fransico Data that has been entered.

    I have included 1 blank model and 1 file with data inputted from San Fransisco Airport.
    Attached Files Attached Files

  119. #119
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    You're going to need to use dynamic named ranges that utilize OFFSET. Take a look here for some more info:

    http://stackoverflow.com/questions/1...spite-range-hi

  120. #120
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by mcmahobt View Post
    You're going to need to use dynamic named ranges that utilize OFFSET. Take a look here for some more info:

    http://stackoverflow.com/questions/1...spite-range-hi
    Gotcha! Ill try and make this work thanks for the idea I'll let you know if I have any questions

  121. #121
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi mcmahobt, I need your help can you please look at this problem for me?

    . I have finalized the requirements for the excel file I have sent to you. Please help me out as soon as you can. Here are the requirements.
    1. All the “Y” values need to be converted into numbers in the “summary” sheet. So for example Keyword building team creates events=1, NO Level 1 examples=2, Ready for Business Review=3, Business Review=4, Legal Review=5, and Production=6

    2. In the Ranking Summary Sheet I have added a “Status” Column that will denote what status each event is in. If one of the events is equal to a 6 then it will say RELEASE. If it is not a 6, then it will denote what number or status it is in from the “Summary” sheet.
    Attached Files Attached Files

  122. #122
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    If requirement 1 is too complicated and tedious you can skip it. Basically requirement 2 is the most important.

  123. #123
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Maybe this is what you are looking for:
    I added a column M to the Summary worksheet and entered this formula in M4 and filled down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    On the Ranking Summary worksheet these totals were carried forward for each event and if 6 were counted the Release is returned or if less than 6 the count is returned:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  124. #124
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I have an updated format, and I also aligned the rows up. Can you apply this formula to the new "sheet" I created in this excel file?
    Attached Files Attached Files

  125. #125
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    The new sheet we will be working on is Ranking Statuses you can see where the status is and that will return either 6 which equals RELEASE and if not it will return the max number of the status of the row in the Summary sheet. Starting from G4.

  126. #126
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I'm taking a bit of a guess here but I think that it may be what you want. I used column M again on the Summary worksheet and totalled the values for y for each row the on the Ranking summary worksheet, I used a formula that if the total of the Ys on the Summary worksheet totalled at least 6 then RELEASE.
    Attached Files Attached Files

  127. #127
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by newdoverman View Post
    I'm taking a bit of a guess here but I think that it may be what you want. I used column M again on the Summary worksheet and totalled the values for y for each row the on the Ranking summary worksheet, I used a formula that if the total of the Ys on the Summary worksheet totalled at least 6 then RELEASE.

    Thank you for getting back to me. I am trying to replace all the Y values with numbers that correspond to the events in row 3, so for example Keyword Building Team Creates Events=1, No Level 1 examples=2, Ready for Business Review=3, Business Review(R)=4, Legal Review(S)=5, Production Release=6. All Y values in those columns should be replaced with those numbers and then the max should be taken to find out how far each event is in completion.

    What your doing seems fine but can you also apply the formula to the "Ranking Statuses" sheet with the requirements I listed above? it will be great help, you can see I have the status column with the blanks.

  128. #128
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    For the COlumn F the "Y" values will not be considered (the Prioritized events) in the Summary sheet

  129. #129
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    The reason why we are not working in the "Ranking Summary" sheet is because when I did the pivot table the events don't line up like in the "Summary" sheet so the statuses are wrong. Use the 'Ranking Statuses" sheet since I pasted the data directly and it lines up with the "Summary" sheet.

  130. #130
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Column F in the Ranking Statuses sheet is where I need to see the statuses like RELEASE or the number of how far the event is.

  131. #131
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    So for example in the Status column for event:

    Supporting Document Regarding Motion For 2004 Examination

    The status would be: 3

  132. #132
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    This solution uses a "mirror" table that reflects the Y values in the Summary worksheet with numeric values. The max is then calculated and the result is referenced by column F. The "mirror" table starts at column AA.
    Attached Files Attached Files

  133. #133
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by newdoverman View Post
    This solution uses a "mirror" table that reflects the Y values in the Summary worksheet with numeric values. The max is then calculated and the result is referenced by column F. The "mirror" table starts at column AA.
    Jackpot! great job!. . I have never heard of the mirror table and I also don't see the mirror function in the F column on the Rank Statuses sheet. Can you explain further in detail how you did this?

  134. #134
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Take a look to the right (starting at column AA). I called it a "mirror" table because it mirrors the contents of the source table only with calculations. If you look at the source table and what I called the "mirror" table, you will see that the number values are in the same positions as the Y values in the source. The formulae in this table simply reference cells starting in the upper left of the data being retrieved. Each column had to have the value Y changed and I did it manually in case you want to make changes to the Y values for each column.

    In column F you will see a very simple formula that just brings the value from the table off to the right into column F. There is nothing complicated about this. I made it as simple as I could so that you could take care of it if something went wrong rather than trying to compose a complicated formula that even I might forget the ins and outs of. This way, the table of data on the source worksheet isn't being touched any more than necessary.

    If you have further questions, feel free to ask.

  135. #135
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by newdoverman View Post
    Take a look to the right (starting at column AA). I called it a "mirror" table because it mirrors the contents of the source table only with calculations. If you look at the source table and what I called the "mirror" table, you will see that the number values are in the same positions as the Y values in the source. The formulae in this table simply reference cells starting in the upper left of the data being retrieved. Each column had to have the value Y changed and I did it manually in case you want to make changes to the Y values for each column.

    In column F you will see a very simple formula that just brings the value from the table off to the right into column F. There is nothing complicated about this. I made it as simple as I could so that you could take care of it if something went wrong rather than trying to compose a complicated formula that even I might forget the ins and outs of. This way, the table of data on the source worksheet isn't being touched any more than necessary.

    If you have further questions, feel free to ask.

    This is great! I didn't see the table to the right but now I do. Thank you for your help I am just beginning to learn advanced functions with excel and your input expands my knowledge. I will be in touch if I need any additional help.

  136. #136
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Hi Newdoverman,

    I am trying to calculate daily averages for each court, I want to display the averages in the Home sheet but I am having a hard time rearranging all the data to show on one sheet can you help me out with how I can approach this and show the averages?

    Thanks! Please get back to me.

  137. #137
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    I'm not sure if you want all the values from the 3 worksheets brought forward to the Home worksheet and average the days from that or to just average each worksheet beside Friday, Saturday and Monday, so I did both.

  138. #138
    Forum Contributor
    Join Date
    02-04-2015
    Location
    Dallas,Texas
    MS-Off Ver
    excel 2013
    Posts
    191

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Quote Originally Posted by newdoverman View Post
    I'm not sure if you want all the values from the 3 worksheets brought forward to the Home worksheet and average the days from that or to just average each worksheet beside Friday, Saturday and Monday, so I did both.

    This works out for me!! your approach is what I was looking for. . .Thank you

  139. #139
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Calculating averages of specific time slots for each day in a given month, please hep?

    Thank you for the feedback.

    If you are satisfied with the solution, please mark the thread as SOLVED. This is found under tools at the top of your first message.

+ 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. help with calculating averages and counts for specific people in a list
    By deniseb27 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-29-2015, 02:23 PM
  2. [SOLVED] calculating hours using scheduled workers and time slots.
    By Darkflame808 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-05-2013, 03:44 AM
  3. [SOLVED] Calculating Averages Over a Time Range
    By adm0104 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-10-2013, 02:30 PM
  4. Calculating Averages of specific products from columns
    By BetaJake in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-24-2012, 06:06 PM
  5. [SOLVED] calculating a time range into predefined time slots
    By DamianWarS in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 07:26 AM

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