=IFS Function Question

%3CLINGO-SUB%20id%3D%22lingo-sub-2231078%22%20slang%3D%22en-US%22%3E%3DIFS%20Function%20Question%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2231078%22%20slang%3D%22en-US%22%3E%3CP%3E%3DIFS(I14%3D0%2C%220%22%2C1%26gt%3B%3DI14%26lt%3B%3D20%2C%221%22%2C21%26gt%3B%3DI14%26lt%3B%3D40%2C%222%22%2C41%26gt%3B%3DI14%26lt%3B%3D60%2C%223%22%2C61%26gt%3B%3DI14%26lt%3B%3D80%2C%224%22%2CI14%26gt%3B%3D81%2C%225%22)%3C%2FP%3E%3CP%3EHow%20do%20I%20do%20this%20for%202016%20Excel%20version%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2231078%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

=IFS(I14=0,"0",1>=I14<=20,"1",21>=I14<=40,"2",41>=I14<=60,"3",61>=I14<=80,"4",I14>=81,"5")

How do I do this for 2016 Excel version?

2 Replies

@ttspivey 

 

=IF(I14=0,"0",IF(I14<=20,"1",IF(I14<=40,"2",IF(I14<=60,"3",IF(I14<=80,"4","5")))))

 

But do you really want the formula to return a text value that looks like a number? You won't be able to sum or average the return values. If you'd prefer to return a number, omit the " " around the return values:

 

=IF(I14=0,0,IF(I14<=20,1,IF(I14<=40,2,IF(I14<=60,3,IF(I14<=80,4,5)))))

 

Alternatively:

 

=(I14>0)+(I14>20)+(I14>40)+(I14>60)+(I14>80)

 

or

 

=SUMPRODUCT(--(F1>{0,20,40,60,80}))

Awesome! Thank you so much!