Change Excel Formula Results With CheckBox

You spent hours creating an impressive table of loan payment calculations. Different loan amounts are across the top of the table, and a variety of terms and interest rates are at the left side. At a glance, you can see the monthly payment for any combination of variables. Sweet!

loancheckbox01

Then, your boss breaks your magical spell of awesomeness, by asking you to include the total payments for each combination. Sure, you could copy that sheet, and tweak the formulas, or add more columns, but then the workbook is

  • double the size, and
  • twice the maintenance.

Use a CheckBox

Thanks to Dave Peterson, there’s a new tutorial and sample file on the Contextures website – Excel Formula CheckBox. Instead of duplicating your work, and creating multiple sheets, you can solve the problem with a simple checkbox.

A checkbox at the top of the worksheet is linked to cell C1. If the box is checked, C1 is TRUE, and if it’s not checked, C1 is FALSE.

The loan payment formulas are modified, to include a reference to cell C1. The the box is checked, the monthly payment is multiplied by the total number of payments. The loan payment table shows the total amount to be repaid, instead of the monthly payment.

loancheckbox02

Other Uses for CheckBox Formulas

Of course, this technique isn’t limited to loan payment tables. You can use a checkbox selector in other workbooks too — for example, let users specify if tax should be included, or check the box if they want to see prices converted to US dollars.

Do you have any other ideas for changing the formula results with a checkbox?

Download the Sample File

For the detailed instructions, please visit the Contextures website – Excel Formula CheckBox. You can download the sample file there too.

___________

You may also like...

2 Responses

  1. kanti says:

    I have used check boxes ad well as radio buttons in Financial Models, where assumptions can be changed. This also helped with Best and Worst Case assessments

  2. Chychyz says:

    Thanks for the pivot table help!!! I have been up all night trying to figure out a simple function and downloaded your addin and it solved my problem right away!

Leave a Reply to kanti Cancel reply

Your email address will not be published. Required fields are marked *