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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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))
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top