“Weight”

Marwan69

New Member
Joined
Mar 14, 2018
Messages
48
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
600
Office Version
2019
Platform
Windows
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

New Member
Joined
Mar 14, 2018
Messages
48
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

Well-known Member
Joined
Dec 9, 2003
Messages
600
Office Version
2019
Platform
Windows
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
ABCDE
1Coffee BeansMonin Blue Ocean syrupMonin Pomergranate syrup Monin Vanilla syrup
2Unit cost
65
57
57
57
3Item unit basis qty (as sold)11700700
4Item unit basis (as sold) [A]kgLmLmL
5Preferred 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
RangeFormula
B2B2=65
C2:E2C2=57
B7:E7B7=B2/B3/B6
 
Last edited:

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
600
Office Version
2019
Platform
Windows
Another attempt...

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

Book2
ABCDE
1Coffee BeansMonin Blue Ocean syrupMonin Pomergranate syrup Monin Vanilla syrup
2Unit cost65575757
3Item unit basis qty (as sold)11700700
4Item unit basis (as sold) [A]kgLmLmL
5Preferred 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
RangeFormula
B7:E7B7=B2/B3/B6
 

Marwan69

New Member
Joined
Mar 14, 2018
Messages
48
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

Well-known Member
Joined
Dec 9, 2003
Messages
600
Office Version
2019
Platform
Windows
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
ABCDE
1Coffee BeansMonin Blue Ocean syrupMonin Pomergranate syrup Monin Vanilla syrup
2Unit cost65575757
3Item unit basis qty (as sold)11700700
4Item unit basis (as sold) (A)kgLmLmL
5Preferred unit basis (B)gmLmLmL
6Conversion factor (A/cf=B)1000100011
7Cost per preferred unit basis
0.065
0.057
0.081428571
0.081428571
Sheet1
Cell Formulas
RangeFormula
B7:E7B7=B2/B3/B6
 

Watch MrExcel Video

Forum statistics

Threads
1,096,183
Messages
5,448,845
Members
405,533
Latest member
Heretical1

This Week's Hot Topics

Top