IFS and AND

Copper Contributor

Hi. Please can you help.

I am using this formula for a scoring system using total scores from 3 worksheets. The scores have a range as per the formula. 

=IFS(C30<=38%,"White",C30<=62%, "Bronze", C30<=87%, "Silver",C30<=100%, "Gold")

However if one of the worksheets totals a “white” score, I need the overall score to be White irrelevant of the scores on the other worksheets. 
Please can you help as to how I add that exception? 

5 Replies

@ClaireH76 

What is the formula you're currently using to calculate the total score?

@Hans Vogelaar on each sheet, I have just used the SUM function to total each score (each question is out of 4) divided by the total possible score for that worksheet, I have then made it a percentage. Then I have used the IFS formula above to tell me gold, silver etc according to the range.

@ClaireH76 

I'm still confused. Is the formula that you posted for one sheet, or is it a summary formula that combines the three sheet scores?

Sorry I know, I'm confusing myself! On the main title page where I want it to display gold, silver etc, I have already totalled the 3 sheets to make one grand total score on cell C30. This is what the formula is working on.

@ClaireH76 

So what is the formula in C30?