- Office 2011 Secrets: Radio B and Check Boxes for Excel
- Creating the Items
- Creating Groups
- Displaying Group Results
- Arranging the Items in Each Group
- Using Control Results in Formulas
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Using Control Results in Formulas
Now comes the fun part. If you’ve opted to display the result for a radio button group or a check box, you can use the result in formulas. Here are two examples for radio buttons.
Displaying the Text of the Selected Button
You can use IF or nested IF functions to display the result text, such as:
=IF(H16=1,"Yes",IF(H16=2,"No","Uncertain"))
In this case, nested Ifs are used to display the choice text, where 1=Yes, 2=No, and 3=Uncertain. The result number is in cell H16. Note that this formula also displays Uncertain when no radio button is selected. If there are only two choices (Yes and No), the formula could be changed to either of the following:
=IF(H16=1,"Yes",IF(H16=2,"No","")) =IF(H16=1,"Yes","No")
If there are several choices, you may prefer to use the CHOOSE function, such as:
=CHOOSE(M19,"yes","no","uncertain")
where M19 is the cell containing the numeric result and the text strings (in quotes) are the values to be displayed for a result of 1, 2, or 3. As you can see, using CHOOSE is considerably less complicated than crafting nested Ifs.
Customer Discount
Here’s a practical example that calculates a customer discount of 0%, 5%, 10%, or 15%, depending on a choice made in a radio button group (Figure 9). The group’s four radio buttons return a result of 1, 2, 3, or 4. The Discount cell (C6) contains the formula:
=ROUND(CHOOSE(F3,0,C5*0.05,C5*0.1,C5*0.15),2)
)
Figure 9 Rather than manually enter the discount for a customer, you can select a discount percentage from a radio button group and let Excel do the computations for you.
The CHOOSE part of the formula examines the radio button result in F3 and multiplies the Subtotal in C5 by 0, .05, .1, or .15, and then the ROUND function rounds it to two decimal places. The calculated discount (C6) is subtracted from the subtotal (C5) to return the total (C7); that is, =C5-C6.