“Weight”

[Marwan69]

Hi,

i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my formula also convers ml & g which I don’t want to be touched. For example: Any smaller unit/weight i want my formula to ignore. How can I incorporate this in my formula?

Many thanks.

 

[Marwan69]

Column A I bought 16 kgs of coffee beans @65. Total is 1,040. Let's ignore the A7 (5% VAT) p;ease. Now I'd like to know how much it costs me for a gram of beans. I'll divide 1040/16kg then convert it into Grams: 1040/16kg/1000. As simple as that.

 

[Marwan69]

Hope is clear

 

[KRice]

Okay, I'm still not sure I understand what the spreadsheet shows. If you are trying to determine the cost of the item per some unit basis (such as g or mL), it doesn't matter how much of the item you purchased. All you need to know is that coffee beans sell for 65 monetary units/kg. So to convert that to a g basis: (65 cost/kg) * (1 kg/1000 g) = 0.065 cost/g

Similarly for the syrups, I believe they cost 57 monetary units/L for one of the syrups, so on a mL basis, that would be (57 cost/L) * (1 L/1000 mL) = 0.057 cost/mL
And the other syrups are sold on a basis of units having a volume of 700 mL, so on a mL basis: (57 cost/700 mL) = 0.0814/mL, or alternatively, you could say:
(57 cost/unit syrup * (1 unit syrup/700 mL) = 0.0814/mL

Your final quantities are fine, I think...although you used costs burdened by VAT.

 

[Marwan69]

Thats exactly what I'm trying to do. Now I want to put all these three results into a formula. I tried to do it but I encountered with the ml and G which I need to divide by the quantity only ie: (57 cost/700 mL). Not like the kgs and Liter which I need to divide by 1000.

 

[KRice]

You might try something like this then, where you show the basis quantity for the item as sold, and then show the units that you would like to convert to...and then the appropriate conversion factor. I've used the codes [A] and to label those units as they relate to the conversion factors. This table doesn't seem to be displaying correctly...I'll post it and check back later to see if there is an issue...

Book1
	A	B	C	D	E
1		Coffee Beans	Monin Blue Ocean syrup	Monin Pomergranate syrup	Monin Vanilla syrup
2	Unit cost	65	57	57	57
3	Item unit basis qty (as sold)	1	1	700	700
4	Item unit basis (as sold) [A]	kg	L	mL	mL
5	Preferred unit basis
Sheet8

[XD=h:c]g[/XD][XD=h:c]mL[/XD][XD=h:c]mL[/XD][XD=h:c]mL[/XD] [XR][XH]6[/XH][XD=h:l|ch:12.9]Conversion factor (A/cf=B)[/XD][XD=h:c]1000[/XD][XD=h:c]1000[/XD][XD=h:c]1[/XD][XD=h:c]1[/XD][/XR][XR][XH]7[/XH][XD=h:l|ch:12.9]Cost per preferred unit basis[/XD][XD=h:c|cls:fx]0.065[/XD][XD=h:c|cls:fx]0.057[/XD][XD=h:c|cls:fx]0.081428571[/XD][XD=h:c|cls:fx]0.081428571[/XD][/XR]

Cell Formulas
Range	Formula
B2	B2	=65
C2:E2	C2	=57
B7:E7	B7	=B2/B3/B6

 

[Marwan69]

I'm not sure I got this. Thanks for your try
Cheers.

 

[KRice]

Another attempt...

The system appears to be acting up...here's a dropbox link to the file:

unitconv.xlsx

Shared with Dropbox

www.dropbox.com

Book2
	A	B	C	D	E
1		Coffee Beans	Monin Blue Ocean syrup	Monin Pomergranate syrup	Monin Vanilla syrup
2	Unit cost	65	57	57	57
3	Item unit basis qty (as sold)	1	1	700	700
4	Item unit basis (as sold) [A]	kg	L	mL	mL
5	Preferred unit basis
Sheet1

[XD=h:c]g[/XD][XD=h:c]mL[/XD][XD=h:c]mL[/XD][XD=h:c]mL[/XD] [XR][XH]6[/XH][XD=h:l|ch:12.9]Conversion factor (A/cf=B)[/XD][XD=h:c]1000[/XD][XD=h:c]1000[/XD][XD=h:c]1[/XD][XD=h:c]1[/XD][/XR][XR][XH]7[/XH][XD=h:l|ch:12.9]Cost per preferred unit basis[/XD][XD=h:c|cls:fx]0.065[/XD][XD=h:c|cls:fx]0.057[/XD][XD=h:c|cls:fx]0.081428571[/XD][XD=h:c|cls:fx]0.081428571[/XD][/XR]

Cell Formulas
Range	Formula
B7:E7	B7	=B2/B3/B6

 

[Marwan69]

I this case I would need to fill in the conversion factor manually which I'd like to create a formula rather than filling the cells manually. Please note that the sheet is large.

 

[KRice]

How many sets of units do you have? You will still need to specify the information in rows 2, 3, and 4, right? But if you have a fairly limited number of units appearing on row 4 and you have a consistent set of preferred units that would appear in row 5, then either a standalone formula could be developed or a lookup table with the conversion factors could be used. Do you have a list of the row 4 units?

I discovered the issue with my earlier posts...I used square brackets around B in cell A5 and that was interpreted as embedded code to make the font bold, and the remainder of my table became corrupted. I've switched to parentheses...here's the XL2BB version. Let me know about the units list.

unitconv.xlsx
	A	B	C	D	E
1		Coffee Beans	Monin Blue Ocean syrup	Monin Pomergranate syrup	Monin Vanilla syrup
2	Unit cost	65	57	57	57
3	Item unit basis qty (as sold)	1	1	700	700
4	Item unit basis (as sold) (A)	kg	L	mL	mL
5	Preferred unit basis (B)	g	mL	mL	mL
6	Conversion factor (A/cf=B)	1000	1000	1	1
7	Cost per preferred unit basis	0.065	0.057	0.081428571	0.081428571
Sheet1

Cell Formulas
Range	Formula
B7:E7	B7	=B2/B3/B6

 

[Marwan69]

The units are fairy the same but the sheet is big.