ClaireNewski
New Member
I am trying to create a dashboard which summarises performance over the year.
Performance is calculated against 2 measures (target and minimum service level - TSL/MSL) and there are 3 outcomes - achieve TSL, achieve MSL, failed.
I need to display the % achievement and whether this meets the TSL, MSL or is a fail. To do this I have used conditional formatting to change the cell colour based on the outcome.
In addition to displaying the % achievement, there are 3 abbrieviations that may be used - NA, NV and NL. This is where the complication comes in.
On my dashboard I want to total how many criteria has met TSL, MSL and failed and how many have been classified as NA, NV and NL by month.
I have tried using the following formula to convert the achievement to either TSL, MSL or failed but it does not recognise the NA, NV and NL entries and counts these as achieving TSL.
=IF(G3>=$E3,"TSL",IF(AND(G3<$E3,G3>=$F3),"MSL",IF(G3<$F3,"Failed")))
How can I amend or rewrite this formula to ignore the NA, NV and NL entries?
I have tried adding a VBA to count the coloured cells but it made everything run too slowly. The workbook hangs for 60 seconds everytime I entered a new cell.
Thanks
Performance is calculated against 2 measures (target and minimum service level - TSL/MSL) and there are 3 outcomes - achieve TSL, achieve MSL, failed.
I need to display the % achievement and whether this meets the TSL, MSL or is a fail. To do this I have used conditional formatting to change the cell colour based on the outcome.
In addition to displaying the % achievement, there are 3 abbrieviations that may be used - NA, NV and NL. This is where the complication comes in.
On my dashboard I want to total how many criteria has met TSL, MSL and failed and how many have been classified as NA, NV and NL by month.
I have tried using the following formula to convert the achievement to either TSL, MSL or failed but it does not recognise the NA, NV and NL entries and counts these as achieving TSL.
=IF(G3>=$E3,"TSL",IF(AND(G3<$E3,G3>=$F3),"MSL",IF(G3<$F3,"Failed")))
How can I amend or rewrite this formula to ignore the NA, NV and NL entries?
I have tried adding a VBA to count the coloured cells but it made everything run too slowly. The workbook hangs for 60 seconds everytime I entered a new cell.
Thanks