Choose Function not returning expected results!

[GeorgieAnne]

Hello My first post here, and I am hoping someone can help me!

I am using the following CHOOSE Function:

=CHOOSE(($D$2<=2)+($D$2=3)+($D$2=4)+($D$2=5)+($D$2>5),5,4,3,1,0)

If cell D2 has a 1 or a 2, I need the CHOOSE function to return 5.

If cell D2 has a 3, I need the CHOOSE function to return 4.

If cell D2 has a 4, I need the CHOOSE function to return 3.

If cell D2 has a 5, I need the CHOOSE function to return 1.

If cell D2 has a value > 5, I need the CHOOSE function to return 0.

Cell D2 will have positive integers between 1 and x. There is no zero values and x is typically less that 10, but it could jump and be as large as 2000.

But the above formula in cell H2 always return 5, and I am not sure why? Even if I change the value of D2 it is always returning 5?

If anyone can help me out I would appreciate it a lot.

I am using Windows 10 64-bit and Office 365 and my Excel version is 2201 Build 16.0.14827.20198

Thanks a Million!

Georgie

 

[Fluff]

Hi & welcome to MrExcel.
You always get 5 as no matter what the value of D2 is this part ($D$2<=2)+($D$2=3)+($D$2=4)+($D$2=5)+($D$2>5) will always return 1
How about like

=($D$2<=2)*5+($D$2=3)*4+($D$2=4)*3+($D$2=5)*1+($D$2>5)*0

 

[jtakw]

Hi,

A couple more suggestions:

Note: F2 formula would be how the CHOOSE function works in your case.

Book3.xlsx
	D	E	F
2	1	5	5
Sheet1010

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(D2,{1,3,4,5,6},{5,4,3,1,0})
F2	F2	=IF(D2>5,0,CHOOSE(D2,5,5,4,3,1))

 

[Fluff]

Another option

=SWITCH(D2,1,5,2,5,3,4,4,3,5,1,0)

 

[GeorgieAnne]

Thank You Fluff, I guess I was following the syntax too much! Your first example worked wonderfully and its initiative enough to relate to the data.

Many Thanks!

Another option

=SWITCH(D2,1,5,2,5,3,4,4,3,5,1,0)

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[GeorgieAnne]

Hi,

A couple more suggestions:

Note: F2 formula would be how the CHOOSE function works in your case.

Book3.xlsx
	D	E	F
2	1	5	5
Sheet1010

Cell Formulas
Range	Formula
E2	E2	=LOOKUP(D2,{1,3,4,5,6},{5,4,3,1,0})
F2	F2	=IF(D2>5,0,CHOOSE(D2,5,5,4,3,1))

Thanks jtakw for the fast response!

Greatly appreciated.

 

[jtakw]

You're welcome, thanks for the feedback.