CHOOSE is one of those Excel functions that may not look useful on their own, but combined with other functions give a number of awesome benefits. At the most basic level, you use the CHOOSE function to get a value from a list by specifying the position of that value. Continue reading
Comments on: Excel CHOOSE function with formula examples
by Svetlana Cheusheva, updated on
Table of contents
Comments page 2. Total comments: 37
Thank you ! very helpful
Hi
I am using CHOOSE with interactive buttons to copy over text which has one character as a superscript. It is not copying over this as superscript but as inline regular text as all other text characters. Is there a way to make CHOOSE do as I would like to or, that CHOOSE cannot copy over text format properties.
Tnx
Khan
Hi, I am using MATCH and CHOOSE to populate a table. Basically, I want CHOOSE to return values to an "11 row and 5 column" table from 52 different tables. Formula is below.
=+CHOOSE(MATCH($B$3,List!$M$5:$M$14), SAMS!$C$4:$G$14, SAMS!$C$17:$G$27,SAMS!$C$30:G$40, SAMS!C$43:G$53,SAMS!$C$56:$G$66, SAMS!$C$69:$G$79,SAMS!$C$82:$G$92, SAMS!$C$95:$G$105, SAMS!$C$108:$G$118, SAMS!$C$121:$G$131). When MATCH returns 1, CHOOSE works. But when it returns any other number, CHOOSE does not work. Can you help? Thanks Ben
Why it always says ''error'' when i put 5 choices?
I also get an error but I'm using 4 choices.
Hi,
I need help to all of you
actually i am creating a incentive calculator for my Sales team
according to our incentive policy
if a sales person achive his monthly target then the total unit multiply with the rate,
here is 2 different situation apply
1st situation
if a person achive his monthly target >100%
for example
a person sale a product 110unit
100 unit is a monthly target
the 100 unit multiply with 100% slab and rest of the 10 unit multiply with >100% or 110% slab.
the slab are also mention here
<80% <89% <99% 100% 110%
56 62.3 69.3 70 87.5 105
kindly suggest me some better option for the calculating of these incentive
Faisal:
If I understand your question here's the formula for the calculation:
=IF(M32<=80,(M32*56),IF(M32<=89,(M32*62.3),IF(M32<=99,(M32*69.3),IF(M32100,(M32*87.5))))))
Enter this into an empty cell and enter the achieved amount in M32. If you want to enter the achieved amount in another cell, change the "M32" addresses in the formula to that cell's address.
You can both simplify the formula and "improve" it by "dynamic coding".
The following formula:
=CHOOSE((B2>0)+(B2>=51)+(B2>=101)+(B2>=151),$E$1,$E$2,$E$3,$E$4)
is better than:
=CHOOSE((B2>0)+(B2>=51)+(B2>=101)+(B2>=151),"Poor", "Satisfactory", "Good", "Excellent")
A reference to a cell is always better than "hard coding".
The same principle applies to the commission example, too.
Best Regards,
Meni Porat
you are right, but it is often said that for high volumes of data it is better to use constants in formulas than references
nice tips... thank's for share.