Adding percentage to cell amount using a checkbox

luke050585

New Member
Joined
Feb 6, 2019
Messages
1
Hi All

First time poster, and very new to excel, so please be kind. :)

This may be a simple question for most of you, but as I'm relatively new to everything in excel bar basic formulas, I'm struggling. Been googling this for hours and can't find an answer (or don't know how to word in correctly in excel!).

I'm trying to help my work produce a document which can easily help us work a price out for items we sell. Our items are custom made, and the price can be affected by several options.

So say we have a base price for the material used in making the item. Say, the material used costs us £0.55. We can then add several options to the material price to increase the price. I'll call these Option A, Option B, Option C and so on. Option A may add 10% to the price. Option B may add 5%. Option C may add another 10%.

I want to create check boxes for the options, so when checked, the percentage gets added onto the original material cost. I will then have a cell where the total cost is displayed after selecting all applicable options.

Is this even possible? If so, how and what do I need to look into to make this possible? Sorry for the amateurish explanation.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
60,325
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
I would tend to go with a lookup table & formulae, rather than checkboxes. Something like

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Price</td><td style=";">A</td><td style=";">B</td><td style=";">C</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Wood</td><td style="text-align: right;;">£0.55</td><td style="text-align: right;;">£0.61</td><td style="text-align: right;;">£0.66</td><td style="text-align: right;;">£0.72</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Metal</td><td style="text-align: right;;">£1.50</td><td style="text-align: right;;">£1.65</td><td style="text-align: right;;">£1.80</td><td style="text-align: right;;">£1.95</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Glass</td><td style="text-align: right;;">£2.00</td><td style="text-align: right;;">£2.20</td><td style="text-align: right;;">£2.40</td><td style="text-align: right;;">£2.60</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Plastic</td><td style="text-align: right;;">£0.23</td><td style="text-align: right;;">£0.25</td><td style="text-align: right;;">£0.28</td><td style="text-align: right;;">£0.30</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Prices</p><br /><br />

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Material</td><td style=";">Option</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">wood</td><td style=";">A</td><td style="text-align: right;;">£0.61</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Glass</td><td style=";">C</td><td style="text-align: right;;">£2.60</td></tr></tbody></table><p style="width:4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Quote</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=INDEX(<font color="Blue">Prices!$C$2:$E$5,MATCH(<font color="Red">$A2,Prices!$A$2:$A$5,0</font>),MATCH(<font color="Red">$B2,Prices!$C$1:$E$1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,360
Check Boxes are linked to a cell you specify in the Format Control, e.g. enter A1
A1 will produce TRUE or FALSE depending on the value of the Check Box

So if you have 3 Check Boxes and you specify the cells A1 B1 and C1, those cells will be TRUE or FALSE

So your formula would be

=0.55+0.55*(A1*10%+B1*5%+C1*10%)

So if all the checkboxes are ticked the formula would be

0.55+0.55*25%

Or you could have the formula as

=0.55*(1+A1*10%+B1&5%+C1*10%)
 
Last edited:

Forum statistics

Threads
1,137,152
Messages
5,679,899
Members
419,861
Latest member
AceDaMace

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