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!
Bookmarks