Checkboxes to calculate tax

city_girl4you

New Member
Joined
Oct 15, 2006
Messages
7
Hi All,

I'm hoping somebody can help me. I created a template of a contract and within it I have created checkboxes to indicate which taxes to apply to the total. In some cases the customer would be exempt from paying one or more taxes. There is a checkbox for each tax.

I don't know how to do the calculations if the box is checked. For instance:

Checkbox 1 - (PST exempt?) if they are then $0.00 if they're not, then Subtotal x 8%

Checkbox 2 - (GST exempt?) if they are then $0.00, if they're not, then Subtotal x 6%

I'm so frustrated trying to get this to work. HELP!!! :cry:
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Right click on the check box and select the Contol Tab. Link the control with a cell, e.g. A1.

Now assuming the subtotal is in B1 you can use a formula like this:

=IF(A1=TRUE,0,B1*0.08)

The linked cells can be out in a hidden column so as they're out of sight.

Dom
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
Right-click on your checkbox. Select Format Control, then the Control tab. In the Cell link: box, enter a cell address of a cell that is out of view of the invoice (cells H11 and H12, respectively, in the sample below). Now, if either of the checkboxes are checked, their respective linked cells will return TRUE. If not checked, the cells will return FALSE.

Now, you can use these formulas (which refer to the sample below):
Formula in C11 is: =IF(H11=TRUE,"",$C$10*B11)
Formula in C12 is: =IF(H12=TRUE,"",$C$10*B12)

The sample below has both of the boxes un-checked.
Book1
ABCDEFGH
9
10Subtotal100
11PST Tax8%8FALSE
12GST Tax6%6FALSE
13114
14
Sheet1


EDIT: Dom, you're just too fast for me. Must be all of those muscles. :)
 

city_girl4you

New Member
Joined
Oct 15, 2006
Messages
7
Thanks A BUNCH!!

Thanks soooooo much Domski and Barry. It worked!! Thanks again. It was greatly appreciated. :biggrin: :biggrin: :biggrin:
 

Forum statistics

Threads
1,137,340
Messages
5,680,916
Members
419,945
Latest member
Carrie Sellers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top