CODEWITHSUNDEEP
excelexcel-formularangeformula
M.Allman
M.Allman

Reputation: 11

Excel IFS formula - range of number

I have a value in cell AB8 and I want to conditionally return a status depending on which value it takes between 1 and 30.

I want to save time and rather than type the scores 1-9 indivdually to turn into "EMERGING" (etc) is there a way I can put a range in?

IFS(AB8=1,"EMERGING",AB8=2,"EMERGING",AB8=3,"EMERGING",AB8=4,"EMERGING",AB8=5,"EMERGING",AB8=6,"EMERGING",AB8=7,"EMERGING",AB8=8,"EMERGING",AB8=9,"DEVELOPING",AB8=10,"DEVELOPING",AB8=11,"DEVELOPING",AB8=12,"DEVELOPING",AB8=13,"DEVELOPING",AB8=14,"DEVELOPING",AB8=15,"DEVELOPING",AB8=16,"DEVELOPING",AB8=17,"SECURING",AB8=18,"SECURING",AB8=19,"SECURING",AB8=20,"SECURING",AB8=21,"SECURING",AB8=22,"SECURING",AB8=23,"SECURING",AB8=24,"SECURING",AB8=25,"READY",AB8=26,"READY",AB8=27,"READY",AB8=28,"READY",AB8=29,"READY",AB8=30,"READY")

Upvotes: 1

Views: 3144

Answers (3)

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

This one might also work,

=IF(AND(AB8>=1,AB8<=8),"EMERGING",IF(AND(AB8>=9,AB8<=16),"DEVELOPING",IF(AND(AB8>=17,AB8<=24),"SECURING",IF(AB8<=30,"READY","INVALID NUMBER"))))

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

Try this:

=INDEX({"EMERGING","DEVELOPING","SECURING","READY"},MATCH(AB8,{0,9,17,25},1))

Or create this table in, say, A1:B4:

0   Emerging
9   Developing
17  Securing
25  Ready

And then the formula is just:

=INDEX($B$1:$B$4,MATCH(AB8,$A$1:$A$4,1))

Upvotes: 1

Mollerup
Mollerup

Reputation: 1

CallumDA's answer is more clean.

But this can be accomplished with nested IF formulas.

=IF(AB8>=1,IF(AB8<=8,"EMERGING",IF(AB8<=16,"DEVELOPING",IF(AB8<=24,"SECURING",IF(AB8<=30,"READY")))))

Upvotes: 0

Related Questions