Choose Formula

[mpapreja]

Dear Experts

I am using a choose Formula as follows:

CHOOSE((VALUE(F2733)>0)+(VALUE(F2733) > 50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000),0.65,0.6,0.55,0.5,0.45,0.4)

At the end of formula I have few fixed factors which i want to name as Set A, Set B, Set C as follows:-

[TABLE="width: 223"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Set A[/TD]
[TD]0.65,0.6,0.55,0.5,0.45,0.4[/TD]
[/TR]
[TR]
[TD]Set B[/TD]
[TD]0.7,0.65,0.6,0.55,0.5,0.45[/TD]
[/TR]
[TR]
[TD]Set C[/TD]
[TD]0.6,0.55,0.5,0.45,0.4,0.35
[/TD]
[/TR]
</tbody>[/TABLE]


Now I want to use the choose formula as follows for various sets as state above:-

CHOOSE((VALUE(F2733)>0)+(VALUE(F2733) > 50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000),Set A)

CHOOSE((VALUE(F2733)>0)+(VALUE(F2733) > 50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000),Set B)

CHOOSE((VALUE(F2733)>0)+(VALUE(F2733) > 50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000),Set C)

Is it possible? Can somebody help in this regard?

 

[Fluff]

Hi & welcome to MrExcel
Maybe something like
=IF(VALUE(F4)>0,0.65-(SUM(VALUE(F4)>50,VALUE(F4)>145,VALUE(F4)>500,VALUE(F4)>750,VALUE(F4)>1000)*0.05),"")
=IF(VALUE(F4)>0,0.7-(SUM(VALUE(F4)>50,VALUE(F4)>145,VALUE(F4)>500,VALUE(F4)>750,VALUE(F4)>1000)*0.05),"")
=IF(VALUE(F4)>0,0.6-(SUM(VALUE(F4)>50,VALUE(F4)>145,VALUE(F4)>500,VALUE(F4)>750,VALUE(F4)>1000)*0.05),"")

 

[mpapreja]

Thanks for responding promptly to resolve my problem. Pl. be informed that my formula

CHOOSE((VALUE(F2733)>0)+(VALUE(F2733) > 50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000),0.65,0.6,0.55,0.5,0.45,0.4)

is working perfectly as per my requirement. The only thing i want to enhance in this formula is to replace "0.65,0.6,0.55,0.5,0.45,0.4" with variables like SetA, SetB and so on, so that whenever i make changes in SetA my choose formula should be updated accordingly.

Hope I am more clear now.

Hi & welcome to MrExcel
Maybe something like
=IF(VALUE(F4)>0,0.65-(SUM(VALUE(F4)>50,VALUE(F4)>145,VALUE(F4)>500,VALUE(F4)>750,VALUE(F4)>1000)*0.05),"")
=IF(VALUE(F4)>0,0.7-(SUM(VALUE(F4)>50,VALUE(F4)>145,VALUE(F4)>500,VALUE(F4)>750,VALUE(F4)>1000)*0.05),"")
=IF(VALUE(F4)>0,0.6-(SUM(VALUE(F4)>50,VALUE(F4)>145,VALUE(F4)>500,VALUE(F4)>750,VALUE(F4)>1000)*0.05),"")

 

[Rick Rothstein]

The only thing i want to enhance in this formula is to replace "0.65,0.6,0.55,0.5,0.45,0.4" with variables like SetA, SetB and so on, so that whenever i make changes in SetA my choose formula should be updated accordingly.

You cannot do what you want with the CHOOSE function... its argument is a comma delimited list which, like all functions with such arguments, cannot be "broken" into separate parts. You can use the INDEX function to achieve the functionality that you want though. First, bring up the Name Manager dialog box (located on the Formula tab, Defined Names group), then click the "New..." button and type SetA in the "Name" field and ={0.65,0.6,0.55,0.5,0.45,0.4} in the "Refers to" field, then click OK. While the dialog box is still up, repeat the above process for SetB and SetC. Now that you have the defined names, you can use this formula for SetA...

[table="width: 500"]
[tr]
	[td]=INDEX(SetA,VALUE(F2733)>0)+(VALUE(F2733)>50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000)[/td]
[/tr]
[/table]

And you can now replace SetA with either SetB or SetC and it will work as your original formula did. Note that this version of the INDEX function is using the identical arguments you wanted to use for the CHOOSE function except that their positions in the argument list are reversed.

 

[mpapreja]

Thanks Rick but I am not getting desired results Following are the results using index function

(INDEX({0.7,0.68,0.67,0.65,0.6,0.55},VALUE(C7)>0)+(VALUE(C7)>50)+(VALUE(C7)>145)+(VALUE(C7)>500)+(VALUE(C7)>750)+(VALUE(C7)>1000))

if is use {0.7,0.68,0.67,0.65,0.6,0.55} as one of the sets:-

<colgroup><col style="width:48pt" width="64" span="2"> </colgroup><tbody>
[TD="class: xl65, width: 64"]Value[/TD]
[TD="class: xl65, width: 64"]Result
[/TD]

[TD="class: xl65"]1
[/TD]
[TD="class: xl66"] 0.70
[/TD]

[TD="class: xl65"]51[/TD]
[TD="class: xl66"] 1.70 [/TD]

[TD="class: xl65"]150[/TD]
[TD="class: xl66"] 2.70 [/TD]

[TD="class: xl65"]501[/TD]
[TD="class: xl66"] 3.70 [/TD]

[TD="class: xl65"]751[/TD]
[TD="class: xl66"] 4.70 [/TD]

[TD="class: xl65"]1001[/TD]
[TD="class: xl66"] 5.70 [/TD]

</tbody>

whereas the result is intended to be as follows using Choose function

(CHOOSE((VALUE(C7)>0)+(VALUE(C7) > 155)+(VALUE(C7)>315)+(VALUE(C7)>500)+(VALUE(C7)>750)+(VALUE(C7)>1000),0.7,0.68,0.67,0.65,0.6,0.55)):-

[TABLE="width: 128"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Value[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] 0.70[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD] 0.70[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD] 0.70[/TD]
[/TR]
[TR]
[TD]501[/TD]
[TD] 0.65[/TD]
[/TR]
[TR]
[TD]751[/TD]
[TD] 0.60[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD] 0.55[/TD]
[/TR]
</tbody>[/TABLE]

You cannot do what you want with the CHOOSE function... its argument is a comma delimited list which, like all functions with such arguments, cannot be "broken" into separate parts. You can use the INDEX function to achieve the functionality that you want though. First, bring up the Name Manager dialog box (located on the Formula tab, Defined Names group), then click the "New..." button and type SetA in the "Name" field and ={0.65,0.6,0.55,0.5,0.45,0.4} in the "Refers to" field, then click OK. While the dialog box is still up, repeat the above process for SetB and SetC. Now that you have the defined names, you can use this formula for SetA...

[TABLE="width: 500"]
<tbody>[TR]
[TD]=INDEX(SetA,VALUE(F2733)>0)+(VALUE(F2733)>50)+(VALUE(F2733)>145)+(VALUE(F2733)>500)+(VALUE(F2733)>750)+(VALUE(F2733)>1000)
[/TD]
[/TR]
</tbody>[/TABLE]

And you can now replace SetA with either SetB or SetC and it will work as your original formula did. Note that this version of the INDEX function is using the identical arguments you wanted to use for the CHOOSE function except that their positions in the argument list are reversed.

 

[Rick Rothstein]

Thanks Rick but I am not getting desired results Following are the results using index function

(INDEX({0.7,0.68,0.67,0.65,0.6,0.55},VALUE(C7)>0)+(VALUE(C7)>50)+(VALUE(C7)>145)+(VALUE(C7)>500)+(VALUE(C7)>750)+(VALUE(C7)>1000))

if is use {0.7,0.68,0.67,0.65,0.6,0.55} as one of the sets:-

<tbody>
[TD="class: xl65"]Value[/TD]
[TD="class: xl65, width: 64"]Result[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl66"] 0.70 [/TD]

[TD="class: xl65"]51[/TD]
[TD="class: xl66"] 1.70[/TD]

[TD="class: xl65"]150[/TD]
[TD="class: xl66"] 2.70[/TD]

[TD="class: xl65"]501[/TD]
[TD="class: xl66"] 3.70[/TD]

[TD="class: xl65"]751[/TD]
[TD="class: xl66"] 4.70[/TD]

[TD="class: xl65"]1001[/TD]
[TD="class: xl66"] 5.70[/TD]

</tbody>

whereas the result is intended to be as follows using Choose function

(CHOOSE((VALUE(C7)>0)+(VALUE(C7) > 155)+(VALUE(C7)>315)+(VALUE(C7)>500)+(VALUE(C7)>750)+(VALUE(C7)>1000),0.7,0.68,0.67,0.65,0.6,0.55)):-

[TABLE="width: 128"]
<tbody>[TR]
[TD]Value[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] 0.70[/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD] 0.70[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD] 0.70[/TD]
[/TR]
[TR]
[TD]501[/TD]
[TD] 0.65[/TD]
[/TR]
[TR]
[TD]751[/TD]
[TD] 0.60[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD] 0.55[/TD]
[/TR]
</tbody>[/TABLE]

You were not using the same logical expression in the two formula. Assuming your CHOOSE function formula is correctly structured, here is what your INDEX function formula should be...

[table="width: 500"]
[tr]
	[td]=INDEX({0.7,0.68,0.67,0.65,0.6,0.55},(VALUE(C7)>0)+(VALUE(C7) > 155)+(VALUE(C7)>315)+(VALUE(C7)>500)+(VALUE(C7)>750)+(VALUE(C7)>1000))[/td]
[/tr]
[/table]

 

[mpapreja]

Perfect!! It was my mistake but now it is working. One more related question if you can help!

i am hard coding various conditions like C7>0, C7>155, C7>315, C7>500, C7>750, C7>1000 ........ in the formula being used.

is it possible someway to link these conditions with my various sets withing the suggested formula like

Set A means
0.7,0.68,0.67,0.65,0.6,0.55 and 0,155,315, 500,750,1000

Set B Means
0.7,0.68,0.67,0.65,0.6,0.55 and 0,50,145, 500, 750,1000

Many Thanks for your assistance. Also confirm how to mark the above answer as an answer.