Formula Help (for an older person)

[apmitch38]

Good Afternoon, can someone please help on a formula? I have a row of 12 months,



When I do an audit I will mark the month with the number of audits, the target is four (4) audits a year. What I have done is add the total number of audits in Column Q. In column R my formula is =sum(Q3)/4, this is fine if I do four or less audits a year but if I do 4+ audits the number goes 100% Plus. (120% or 140%) etc. What formula do you suggest I put in cell R to give me a figure of only 100% (Or any other suggestions??)

 

[NARAYANK991]

Hi ,

Do you mean to say that if the number of audits in a year is more than 4 , then the percentage adherence in column R should be limited to 100 % ?

There are two ways to do this :

1. Restrict the total count of audits to a maximum of 4

2. Restrict the percentage result to a maximum of 100 %

You can have a formula such as :

=MIN(4,Q3)/4

=MIN(100% , Q3/4)

Narayan

 

[apmitch38]

Thank you for your help, much appreciated.