Simple formula but my brains not working please help

[adamdarwin315]

Hi, as a sideline I make candles and I'm trying to do a spread sheet to work out the cost of each candle. I'm struggling to find the formula to price the scent thst I add as a variable amount. Say for instance I paid £5 for 50g of scent and added it to 700g of wax, what would the price be per gram of the whole 750g and what would the formula be.
I've added the price of the wax elsewhere.
I'm new to excel (obviously) it's probably easy, but my brain doesnt work.

 

[Joe4]

Welcome to the Board!

We would need to know how the price for the Wax is represented.
You have the scent is £5 for 50g.
How is the price of Wax set?

 

[MARK858]

It would be (the cost of the wax + £5) / 750 wouldn't it?

 

[Joe4]

It would be (the cost of the wax + £5) / 750 wouldn't it?

It would, if the price he has is the cost for exactly 700g of Wax.
If the price of the max he has is for anything other than exactly 700g of Wax (i.e. he has price per 500g of Wax), then a little more math would be needed.

 

[DanteAmor]

Hi @adamdarwin315, welcome to the forum, check the following example:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:126.42px;" /><col style="width:100.75px;" /><col style="width:76.04px;" /><col style="width:149.23px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Price</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Gram</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">Price per gram</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff00; ">Wax</td><td style="text-align:right; ">8</td><td style="text-align:right; ">700</td><td style="text-align:right; ">0.0114</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; ">Scent</td><td style="text-align:right; ">5</td><td style="text-align:right; ">50</td><td style="text-align:right; ">0.1000</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; ">TOTAL</td><td style="text-align:right; ">13</td><td style="text-align:right; ">750</td><td style="text-align:right; ">0.1114</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=B2/C2</td></tr><tr><td >D3</td><td >=B3/C3</td></tr><tr><td >B4</td><td >=SUM(B2:B3)</td></tr><tr><td >C4</td><td >=SUM(C2:C3)</td></tr><tr><td >D4</td><td >=SUM(D2:D3)</td></tr></table></td></tr></table>

 

[Joe4]

Dante,

I believe your math is a bit faulty. D4 should actually be:

=B4/C4

otherwise, you are weighting the different amounts equally, and that is not the case.

 

[DanteAmor]

Dante,

I believe your math is a bit faulty. D4 should actually be:

=B4/C4

otherwise, you are weighting the different amounts equally, and that is not the case.

Hi @Joe4, You're right


Hi @adamdarwin315, As Joe said, the formula in D4 should be
=B4/C4

 

[MARK858]

...

It would, if the price he has is the cost for exactly 700g of Wax.

I've added the price of the wax elsewhere

 

[Joe4]

I've added the price of the wax elsewhere.

I saw that, but they did not give us any indication of what units the price was stored in (i.e. price per gram. price per 700 gram, price per some other quantity), hence my initial question.