+ Reply to Thread
Results 1 to 7 of 7

Formula to extract the highest peak date and peak times from a consolidated report

  1. #1
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Formula to extract the highest peak date and peak times from a consolidated report

    I have a report that has call volumes by interval...I need to know at which date and time of the day there was a spike in the call volume. Is there a formula that can pull from the consolidated report for an entire year the peak day and peak time?

    Anyone?

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula to extract the highest peak date and peak times from a consolidated report

    attach a sample excel file
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: Formula to extract the highest peak date and peak times from a consolidated report

    i've attached the sample file
    Attached Files Attached Files

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to extract the highest peak date and peak times from a consolidated report

    Please see attached file with formula in M2
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula to extract the highest peak date and peak times from a consolidated report

    L2=INDEX(Table1[Date],MATCH(MAX(INDEX((COUNTIF(L$1:L1,Table1[Date])=0)*Table1[Calls Offered],0)),INDEX((COUNTIF(L$1:L1,Table1[Date])=0)*Table1[Calls Offered],0),0))
    M2=INDEX(Table1[Interval],MATCH(MAX(INDEX((COUNTIF(L$1:L1,Table1[Date])=0)*Table1[Calls Offered],0)),INDEX((COUNTIF(L$1:L1,Table1[Date])=0)*Table1[Calls Offered],0),0))

    Try this and copy towards down
    Last edited by samba_ravi; 04-30-2015 at 01:14 PM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to extract the highest peak date and peak times from a consolidated report

    If I can be so bold... I think that you've been asked the wrong question. Taken day-on-day the numbers are small. I would calculate the overall number of calls by time of day and then look for a trend. See the cells in green.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Forum Contributor
    Join Date
    04-28-2013
    Location
    Miami, United States
    MS-Off Ver
    Excel 365 (Newest version)
    Posts
    254

    Re: Formula to extract the highest peak date and peak times from a consolidated report

    Glenn Kennedy IF i have to match the totals to the month what do I do?

+ 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. Electricity Calculations based on Peak and Off Peak Rate
    By mrwrighty in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2014, 07:43 AM
  2. Counting the peak & non peak hours between two dates?
    By zeroprobe in forum Excel General
    Replies: 3
    Last Post: 12-09-2013, 06:53 AM
  3. Replies: 5
    Last Post: 05-18-2013, 11:59 AM
  4. Formulas - converting dBdsx to Volts peak to peak
    By alex148 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-11-2007, 09:49 AM
  5. Replies: 1
    Last Post: 03-13-2006, 07:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1