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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,676
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


Excel 2013/2016
ABCDE
1PriceABC
2Wood£0.55£0.61£0.66£0.72
3Metal£1.50£1.65£1.80£1.95
4Glass£2.00£2.20£2.40£2.60
5Plastic£0.23£0.25£0.28£0.30
Prices



Excel 2013/2016
ABC
1MaterialOption
2woodA£0.61
3GlassC£2.60
Quote
Cell Formulas
RangeFormula
C2=INDEX(Prices!$C$2:$E$5,MATCH($A2,Prices!$A$2:$A$5,0),MATCH($B2,Prices!$C$1:$E$1,0))
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,426
Office Version
  1. 2019
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,175,938
Messages
5,900,424
Members
434,832
Latest member
bornsteij78

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