+ Reply to Thread
Results 1 to 5 of 5

Percentage to Date Formula

  1. #1
    Registered User
    Join Date
    05-22-2020
    Location
    Bristol, England
    MS-Off Ver
    MS Office for 365
    Posts
    2

    Question Percentage to Date Formula

    Hello All,

    Hoping for some advice - Excel novice here, that's at her wit's end!

    I'm developing a compliance tracker, which calculates the % of compliance targets met, in a number of different categories across 26 buildings. Each compliance item is broken down into % of days/months where targets have been met (Row 27 & 33), feeding into column P which calculates the total % of compliance for all days/months. The issue is that this Totals column (P) takes into account all data for the entire year Sept 20-Aug 21 - meaning, if I want to check how compliant a building is in January, the total % will only ever appear as a max of 50%, as the data for Feb-Aug has not yet been input.

    Does anyone know a formula to calculate Total % To Date e.g. the compliance year runs from Sep 20-Aug 21, if I want to check how well we're hitting targets on 1st Oct 20, this formula would reflect that we are 98% compliant if 98% of data has been input in September? In an ideal world, I'd then be able to apply a rule to the sheet that states that if all data for June has not been input by June 30th, the column will turn orange on July 1st, and so on.

    Hope this makes sense - I have attached a small example from the sheet.

    Any help or advice with this head-scratcher would be much appreciated - thank you!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Percentage to Date Formula

    Hi
    - please add some data and expected results
    - best remove all the merged cells they are nothing but trouble

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,682

    Re: Percentage to Date Formula

    i have used a SUMIFS() and a countif()
    as an example - is this the sort of thing you were after

    you taking an average of the % , so i assume thats what you are after but based on Date
    We can change so it does not count May , as today() is higher than 1st may , at the moment it does , but i thought i would check we are on the right track, but we could use End of month function EOMONTH()

    See cells
    R27 for the average
    then
    T27 for the sum based on today dates
    U27 for the count base don todays date
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Percentage to Date Formula

    Hi,

    1st of all I changed your formula in D27-O27 to ignore N/As, now you can drag the formula along the row.

    D27 =COUNTIFS(D2:D26,"<>N/A",D2:D26,"<>")/COUNTIF(D2:D26,"<>N/A")D

    The formula in P27 will calculte only the number of months where input has been entered.

    =SUMIF(D27:O27,"<>0")/COUNTIF(D27:O27,"<>0")
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-22-2020
    Location
    Bristol, England
    MS-Off Ver
    MS Office for 365
    Posts
    2

    Re: Percentage to Date Formula

    Hugely appreciative to everyone for their responses - Etaf, that is exactly the formula I was after, thank you so much.

    Thanks also to Belinda, I've now learnt how to ignore certain cells so I don't have to manually select each range! Thumbs up for saving time!

    You're all superstars

+ 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. [SOLVED] formula to help with percentage for month to date
    By dougers1 in forum Excel General
    Replies: 9
    Last Post: 09-01-2016, 07:32 AM
  2. [SOLVED] Formula to calculate percentage based on date and Value
    By bnwash in forum Excel General
    Replies: 10
    Last Post: 05-02-2015, 05:13 AM
  3. Replies: 3
    Last Post: 09-04-2014, 08:17 PM
  4. Replies: 1
    Last Post: 02-20-2014, 05:33 PM
  5. Replies: 4
    Last Post: 01-17-2013, 01:23 PM
  6. Replies: 3
    Last Post: 08-14-2012, 05:14 AM
  7. Formula to return date that a Percentage is reached.
    By lucraft in forum Excel General
    Replies: 0
    Last Post: 07-19-2011, 09:47 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