Excel Question - Logical Operators

[doknights]

I am currently doing a performance review and I am attempting to create a formula that allows the sheet to calculate if the below targets were met

Most Goals not achieved 85% or ≥3 missed days.
Some Goals achieved 90% or 2 missed days.
Nearly all Goals achieved 95% or 1 missed day.
All Goals achieved 100% or no missed days.
All Goals achieved with some exceeding 100% or no missed days for 3 months consecutive

I have tried this formula on the sheet but it is only calculating the 40 points but not the 50 points which take into consideration the 3 months. Why?

=IF(ISBLANK(AM$116),"",SWITCH(TRUE,AM$116=100,40,AM$116>95,30,AM$116>=90,20,AM$116>85,10,AVERAGE(OFFSET($AL$116,0,1,1,3)),50))

 

[Michael M]

I think you need to start with the lowesdt figure first.....Maybe this way

=IF(ISBLANK(AM116),"",IF(AM116<90,10,IF(AM116<95,20,IF(AM116<100,30,IF(AM116=100,40,50)))))

 

[doknights]

I think you need to start with the lowesdt figure first.....Maybe this way

=IF(ISBLANK(AM116),"",IF(AM116<90,10,IF(AM116<95,20,IF(AM116<100,30,IF(AM116=100,40,50)))))

Thanks but this does not take into consideration the final metric "All Goals achieved with some exceeding 100% or no missed days for 3 months consecutive". This is why I had the average offset in my function to take into consideration the 3 month performance.