Help with formula for more than one condition

Copper Contributor
I've created a spreadsheet where scores are inputted. I've totalled that in one cell and converted this to a grade in another cell using a formula. The problem I've got is that some cells are more important so if they're not achieved the grade given needs to be one lower than the one given using the formula.
I've been trying to create a formula that allows for this. I can't make one that allows for all the different grade options: this works for one level but when I try to add to it, it no longer works. I need it to work for five different levels and change the grade if the value is less than 6.
=IF(D25="3DEV",D26<=6,"3EM")
Thank you for your help!
8 Replies

@MMAHSAT 

Perhaps you may provide small sample file with manually added desired result, so far it's not very clear what is required.

@Sergei Baklan Thanks.  I've added this spreadsheet with the correct result in the bottom cells.  I hope this makes more sense.

Thanks

 

Nothing wrong with formula in cell B24 & C24. Also formula in B26 working properly,, you have not dragged the formula in C26, otherwise in both cells you get 3 BEG,,, now let me say I'm unable to realize what exactly you are looking for,, please be specific all about the issue.

C26 and B26 need to give different results even though they have the same score as B6 and B7 have to have at least 3 points in each (can have a total of 4points in each). I was asked to add manually what result I wanted so in C26 I need "3 EM". I need a longer formula than the successful one in B26 as there are five grades which need to be lowered by one grade if the total of B6 and B7 (which is in B25) is less than 6. Whenever I try to add more conditions the cell comes back with true or error. I need a formula to change the grade to one below if the total of B25 is less than 6. Hope this makes it clear. Thanks

@MMAHSAT 

I'd suggest to add helper range in any place of your workbook as

image.png

with it formula could be

=LOOKUP(SUM(B$5:B$22),$G$7:$G$12,CHOOSE(((B$5+B$6)>=6)+1,$H$6:$H$11,$H$7:$H$12))

which returns

image.png

Brilliant thank you so much!
I think,, being a OP (original post owner) one must have upload proper data with illustration all about the exact need,,, coz as answerer ,,, we pick from the shared data,, assumption sometimes back fires. ☺

@MMAHSAT , you are welcome