Sum for grams, cups and monies.

[Geek Girl 007]

In column J I want it to show the total amount the cost would be for regarding the amount of cups in column H.
Can this be done?

Roberta's Accounts.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2				Food Items
3		Unit			Unit			Unit		Per 20
4	Product	type	Weight	Num	Total	Price	Per-item	Cup		Chocolates
5	Chocolate-Dark	g	1000	1	1000 g	£24.95	£24.95	0.5	125 g
6	Chocolate-Milk	g	0.75	6	4.5 g	£12.99	£2.17	1	250 g
Domes

Cell Formulas
Range	Formula
E5:E6	E5	=D5*C5&" "&B5
G5:G6	G5	=F5/D5
I5:I6	I5	=250*H5&" "&B5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E153	Cell	contains an error	text	NO
G5:J153	Cell	contains an error	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B5:B6	List	=MAIN!$E$2:$E$5

 

[Micron]

Not sure what you're after. An expression (formula) that calculates the cost of an ingredient used based on its unit and unit price? If so, then the expected answer for J5 would be £3.11875 ?

 

[Geek Girl 007]

Not sure what you're after. An expression (formula) that calculates the cost of an ingredient used based on its unit and unit price? If so, then the expected answer for J5 would be £3.11875 ?

Hi There,
I buy the products, they mostly come in grams, most of my chocolate recopies come in cups, I'm trying to work out how much it would cost to make a batch, to see if I can make a profit, does that make sense???

I am hoping for a formula to work this out automatically, that's why i added the extra columns.
Thank you

 

[Micron]

You didn't answer my question. If I5 is the quantity used in a recipe (in grams) and you pay 24.95 for 1000 grams then the cost of that item in the recipe should be 3.11875. That is simply (125*24.95)/1000 or using cell addresses for that item, I5*F5/E5. It should not matter how you bracket that, or if you bracket that at all.
(125*24.95)/1000
125*(24.95/1000)
125*24.95/1000
should all provide the same result because there's no subtraction or addition involved. You do that for every ingredient line, then add those calculations to get the total cost of the ingredients. What that won't answer about profit is how much energy (electricity/gas) you put into it. Nor the wear and tear on equipment such as a mixer.

 

[Geek Girl 007]

You didn't answer my question. If I5 is the quantity used in a recipe (in grams) and you pay 24.95 for 1000 grams then the cost of that item in the recipe should be 3.11875. That is simply (125*24.95)/1000 or using cell addresses for that item, I5*F5/E5. It should not matter how you bracket that, or if you bracket that at all.
(125*24.95)/1000
125*(24.95/1000)
125*24.95/1000
should all provide the same result because there's no subtraction or addition involved. You do that for every ingredient line, then add those calculations to get the total cost of the ingredients. What that won't answer about profit is how much energy (electricity/gas) you put into it. Nor the wear and tear on equipment such as a mixer.

Hi again, thank you,
Profit wise, I'm just trying to work out how much it would cost to make a batch up and then see if it's work me adding a profit or just continue giving them out as gifts.
At the moment I am just looking for actual costs (i hadn't even factored in the electricity etc, so thank you for that)

I tried this but it didn't work
=I5*F5/C5

Roberta's Accounts.xlsx
	A		C	D	E	F	G	H	I	J
1
2				Food Items
3			Weight		Unit			Unit		Per 20
4	Product		in grams	Num	Total	Price	Per-item	Cup		Chocolates
5	Chocolate-Dark		1000	1	1000 g	£24.95	£24.95	0.5	125 g	#VALUE!
6	Chocolate-Milk		100	1	100 g	£2.00	£2.00	1	250 g	#VALUE!
Domes

Cell Formulas
Range	Formula
I5:I6	I5	=250*H5&" "&B5
J5:J6	J5	=I5*F5/C5
E5:E6	E5	=D5*C5&" "&B5
G5:G6	G5	=F5/D5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E5:E153,G5:J153	Cell	contains an error	text	NO

 

[Micron]

get rid of the g, otherwise it's not a number, it's a string. Then retest. If everything is in grams, put the g or grams in the header. If not, put the units in j but move j values over to the right somewhere first. Then j values would be g, oz. C (cup), tsp, tblsp or whatever - assuming those are the quantities used in a batch. I don't see the point of column C as you have the info elsewhere. Also, shouldn't A4 be "Ingredient" and Product is the output of the recipe?
EDIT OK, C is feeding a formula. Methinks I'd build this differently.

 

[Geek Girl 007]

get rid of the g, otherwise it's not a number, it's a string. Then retest. If everything is in grams, put the g or grams in the header. If not, put the units in j but move j values over to the right somewhere first. Then j values would be g, oz. C (cup), tsp, tblsp or whatever - assuming those are the quantities used in a batch. I don't see the point of column C as you have the info elsewhere. Also, shouldn't A4 be "Ingredient" and Product is the output of the recipe?
EDIT OK, C is feeding a formula. Methinks I'd build this differently.

Thank you so much for this feedback it is really helpful, especially as this is all new to me.... (not spreadsheets, this process)
I have column C because the is the amount I bought the chocolate and I was trying to work out the price per percentage that a batch of 20 chocolates, I'm not sure how I would re-do it at this point, maybe I'll learn more if this works??? I will take any advice

Thank you so much, yes removing the g worked....

 

[Micron]

Just playing around and no doubt having a different mindset (not better, just different) I was going to suggest that I might do this

but when I got around to adding another recipe I see that a separate table of ingredients would probably be better - columns C (purchase price) to J (metric unit) in that table. Then cols A, B and K and L in the recipe sheet. Or maybe validation lists would be better. Then you could pick an ingredient and add it to the recipe from that list. Other lists going across could allow you to pick out units and costs and then do the calcs on the right side of the sheet. Food for thought. Good luck!

 

[Geek Girl 007]

Just playing around and no doubt having a different mindset (not better, just different) I was going to suggest that I might do this
View attachment 121417
but when I got around to adding another recipe I see that a separate table of ingredients would probably be better - columns C (purchase price) to J (metric unit) in that table. Then cols A, B and K and L in the recipe sheet. Or maybe validation lists would be better. Then you could pick an ingredient and add it to the recipe from that list. Other lists going across could allow you to pick out units and costs and then do the calcs on the right side of the sheet. Food for thought. Good luck!

This looks fantastic, I will defo have a go, that you so much for the feedback.
I truly appreciate it, Thank you