Return a rating based on a number range

[alecto3]

Hi, trying to write a formula that returns one of four text ratings based on what number is displayed in cell D32. Here's what I have : =IF(D32≤34,"Minimal change challenge",IF(AND(D32≥35AND≤50),"Moderate change challenge",IF(AND(D32≥51AND≤75)),"Substantial change challenge",IF(AND(D32≥76),"Significant change challenge"))). Not sure where I'm going wrong, can anyone help?

 

[dermie_72]

=if(D32<34,"Minimal Change challenge",If(And(D32>35,D32<50),"Moderate Change Challenge",if(And(D32>51,D32<75),Substantial Change Challenge",Significant Change Challenge")))

When you use and AND statement in the IF, you need to specify each statement with a comma between
Also you don't need the last IF, if all the other if's are not ture it defaults to the last one.
Hope that helps.

 

[alecto3]

Thanks for this! Still getting an error - I think it may be to do with how the range is represented. I've tried using the greater-than-or-equal-to symbol and I've tried using your formula (D32>35,D32<50) but neither work . Once I get past the first statement it goes wrong.
=IF(D32<34,"Minimal Change challenge") works fine.
But as soon as I add another category an error comes up, i.e.
=IF(D32<34,"Minimal Change challenge"),IF(AND (D32>35,d32<50)"Moderate change challenge")

 

[KRice]

Here is one idea, but I'm not sure what your thresholds are supposed to be...whether they should be > or >=

MrExcel_20240326.xlsx
	D	E
32	51	Substantial Change Challenge
Sheet2

Cell Formulas
Range	Formula
E32	E32	=XLOOKUP(TRUE,D32>={75,50,35},{"Significant","Substantial","Moderate"},"Minimal")&" Change Challenge"

 

[dermie_72]

Thanks for this! Still getting an error - I think it may be to do with how the range is represented. I've tried using the greater-than-or-equal-to symbol and I've tried using your formula (D32>35,D32<50) but neither work . Once I get past the first statement it goes wrong.
=IF(D32<34,"Minimal Change challenge") works fine.
But as soon as I add another category an error comes up, i.e.
=IF(D32<34,"Minimal Change challenge"),IF(AND (D32>35,d32<50)"Moderate change challenge")

=IF(D32<34,"Minimal Change challenge",IF(AND(D32>35,D32<50),"Moderate change challenge",""))
You had a Bracket after the first if, instead of a comma, and no comma after the first and statement. See if the above works, also check out KRice's suggestion

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple of other possible options. The second one is just a slight variation on Kirk's.
I have assumed that the values in col D are whole numbers as your original attempted formula was not trying to allow for values like 34.5

24 03 27.xlsm
	D	E	F
32	36	Moderate change challenge	Moderate change challenge
33	3	Minimal change challenge	Minimal change challenge
34	80	Significant change challenge	Significant change challenge
35	55	Substantial change challenge	Substantial change challenge
Rating

Cell Formulas
Range	Formula
E32:E35	E32	=CHOOSE(MATCH(D32,{0,35,51,76}),"Minimal","Moderate","Substantial","Significant")&" change challenge"
F32:F35	F32	=XLOOKUP(D32,{34,50,75},{"Minimal","Moderate","Substantial"},"Significant",1)&" change challenge"

 

[KRice]

Interesting, Peter. I would have preferred to keep all of the adjectives in a single array or list as you've done with the CHOOSE/MATCH formula, but wasn't sure what the lower bound for the value in D might be. So I ended up using the [if not found] argument in XLOOKUP. But if D<0, then the lower threshold in MATCH needs some attention. I opted for a XLOOKUP(TRUE,D32>=... approach to make the lower threshold definition more explicit and easier to see/revise, but it's not clear to me whether the OP wants D32> or D32>=, and what the realistic lower bound for the D value is. Those details are needed to fine-tune a formula to correctly deal with the thresholds.

 

[Peter_SSs]

But if D<0, then the lower threshold in MATCH needs some attention.

Sure. We don't know what is possible but if negatives are possible, pretty easy to change the lower bound to something that should cope.
eg

=CHOOSE(MATCH(D32,{-9E+99,35,51,76}),"Minimal","Moderate","Substantial","Significant")&" change challenge"

 

[alecto3]

=IF(D32<34,"Minimal Change challenge",IF(AND(D32>35,D32<50),"Moderate change challenge",""))
You had a Bracket after the first if, instead of a comma, and no comma after the first and statement. See if the above works, also check out KRice's suggestion

That works!! Thank you for your help, I did have issues with missing commas but eventually got it to reflect one of four different ratings based on the number range. Very happy, thank you for your help!