+ Reply to Thread
Results 1 to 6 of 6

Rolling 30 + 7 day KPIs

  1. #1
    Registered User
    Join Date
    07-23-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365 Mac
    Posts
    3

    Post Rolling 30 + 7 day KPIs

    Hi all,

    I'd love to get some help with a tricky formula! I'm trying to create rolling 30 day and 7 day scores and have tried the following after seeing it on a forum page:

    =COUNTIFS(Projects!K2:K308,"Yes", Table1[Install date],">="&TODAY()-30,Table1[Install date],"<="&TODAY())/COUNTA(Projects!K2:K308)


    However, this is showing the score out of all projects from all time, rather than just projects in the last 30 days.

    Averages are on the Rolling Totals tab and data is on the Projects tab.

    I've attached the data below (anonymised, of course), and would appreciate any help!

    Thanks,

    Martha
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Rolling 30 + 7 day KPIs

    Welcome to the forum.

    You'll need to tell us what results you are expecting. I can't see what's wrong, and the three sets of summary data show different values, so ... What values do you want them to show?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    07-23-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365 Mac
    Posts
    3

    Re: Rolling 30 + 7 day KPIs

    Ok so the results we're expecting to see is basically as below as an example:

    Out of all the projects with an install date of within the last 30 days (column J), how many were completed within the allotted time marked with a "Yes" (column L).

    We would want that to show up in Rolling Totals G11 as a percentage.

    I think if someone can build the formula just for that one, I should be able to transpose it to the others.

    I hope that all makes sense, please let me know if I can provide any more information!

    Thanks,

    Martha
    Last edited by bfglass; 07-23-2021 at 10:46 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Rolling 30 + 7 day KPIs

    Yes, but which results are wrong, in your opinion? You have given us a workbook with formulae in place that you say don't work. Tell us which cells are wrong and what you expect them to return. A VALUE, not a description.

  5. #5
    Registered User
    Join Date
    07-23-2021
    Location
    Bristol, England
    MS-Off Ver
    Office 365 Mac
    Posts
    3

    Re: Rolling 30 + 7 day KPIs

    Ah I see! Ok so G11 should be 45%

    I believe G5, 7, 9, 11, 13, 15, 17 and J5, 7, 9, 11, 13, 15, 17 on the Rolling Totals tab are incorrect.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Rolling 30 + 7 day KPIs

    As of today it would seem that 8 of the 9 (89%) projects over the past 30 days are Completed within alloted time, so the formula for G11 could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula for J11 could be:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I would encourage you to attempt to rewrite the denominators of the other formulas similarly and let us know if you run into any difficulties or have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Contact Center KPIs
    By stegrid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-09-2020, 03:26 PM
  2. [SOLVED] KPIs of First 30 Days - Unique.
    By ned.f in forum Excel General
    Replies: 12
    Last Post: 08-13-2019, 09:31 AM
  3. Graphical Representation of KPIs
    By shaikhrulez in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-20-2015, 05:21 AM
  4. Service Desk KPIs
    By Harlin67 in forum Excel General
    Replies: 1
    Last Post: 03-18-2015, 07:11 PM
  5. How to generate reports/KPIs from data
    By Juneau333 in forum Excel General
    Replies: 8
    Last Post: 01-09-2015, 06:05 AM
  6. KPIs in Excel
    By Reykjavik in forum Excel General
    Replies: 1
    Last Post: 05-02-2012, 11:36 AM

Tags for this Thread

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