Unit of Measure Conversions

[bschwartz]

Hello friends,

I would appreciate some assistance simplifying a formula for converting units of measure. We have a database called the item master file which contains item codes, descriptions, and various attributes associated with each item. When you import that database to Excel, the items codes run down the rows, and the attributes across the columns in a typical database structure. The item master file has a primary unit of measure, against which all other conversions and calculations take place called the stock unit of measure (or Stock_UM). Examples of units of measure are Pc (piece), Ea (Each), Lb (Pound), Cs (Case), Bx (Box), or Ctr (Container).

There is allowance within the item master file to setup up to two conversions to alternate units of measure (example from Box to Lbs or pieces). An item can have anywhere from 0 to 2 conversions, and if it has one conversion, that can be set up in any of the two conversion slots. Each conversion factor requires (3) variables.

1. An alternate unit of measure
2. A conversion factor
3. A math operation (either multiply or divide the stock UM by the conversion factor to reach the alternate unit of measure)

For example, if the stock unit of measure is LBs and there is 18 LBs in a Box,

1. Alternate unit of measure would be box
2. Conversion factor would be 18
3. Math operation would be multiply.

Multiplication because you need to multiply the stock UM (pounds) by 18 in order to get to a box.

The Item Master will look something like this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	ITEM_NUMBER	DESCRIPTION_1	UM_STOCK	SUB_UNIT_OF_MEASURE	SUB_UNIT_FUNCTION	SUB_CONVERSION_FACTOR	SUB_UNIT_OF_MEASURE2	SUB_UNIT_FUNCTION2	SUB_CONVERSION_FACTOR2
2	0100	Widget 1	CTR		M	0		M	0
3	0101	Widget 2	PC		M	0		M	0
4	0102V	Widget 3	BX	PC	D	12		M	0
5	0103	Widget 4	LB	PC	M	3	BX	M	18
6	0107	Widget 5	LB	BX	M	18	PC	M	3
7	0107D	Widget 6	LB	BX	M	18	PC	M	3
8	0107M	Widget 7	LB	BX	M	3		M	0
9	0108	Widget 8	LB	PC	M	2.5	BX	M	5
10	0109	Widget 9	LB	PC	M	2.5	BX	M	5
11	0110	Widget 10	BX	PC	D	12		M	0
12	0113	Widget 11	LB	PC	M	1.1	BX	M	3
13	0200	Widget 12	LB	BX	M	10	PC	M	0.5
14	0201	Widget 13	LB	BX	M	30	PC	M	5
15	02013	Widget 14	LB	BX	M	18		M	0
16	020132	Widget 15	BX	LB	D	6		M	0
17	02014	Widget 16	LB	BX	M	20		M	0
18	02018	Widget 17	BX	PC	D	12		M	0
19	02018S	Widget 18	BX	PC	D	24		M	0
20	0201P	Widget 19	BX	PC	D	12		M	0
Item_Master

With that explanation in mind, it frequently happens that you are handed a report that uses a specific unit of measure, but you need to convert everything back into the stock unit of measure in order to perform additional functions on the data or to get everything into a single unit of measure, such as pounds.

For example, a sales report given in units sold might look like this (descriptions and sales data are fictitious):

Excel 2010
	A	B	C	D
1	Item Code	Item Description	UOM Pricing	Qty Shipped
2	0100	Widget 1	CTR	75,232
3	0101	Widget 2	PC	143,596
4	0102V	Widget 3	PC	136,619
5	0103	Widget 4	LB	133,651
6	0107	Widget 5	LB	24,318
7	0107D	Widget 6	LB	112,987
8	0107M	Widget 7	LB	208,416
9	0108	Widget 8	LB	15,151
10	0109	Widget 9	LB	108,021
11	0110	Widget 10	PC	59,528
12	0113	Widget 11	LB	36,596
13	0200	Widget 12	LB	87,931
14	0201	Widget 13	LB	102,379
15	02013	Widget 14	LB	8,318
16	020132	Widget 15	BX	46,717
17	02014	Widget 16	LB	78,234
18	02018	Widget 17	PC	72,656
19	02018S	Widget 18	PC	40,505
20	0201P	Widget 19	PC	83,528
Sales Data

To convert back into the stock UM, I am using the following logic:

• If unit of measure = Stock UM, Qty Shipped (D2)
• Otherwise, check for a match to the sub conversion UM.
  • If it is a match multiply or divide by the sub conversion factor
  • Multiply or divide based on the sub conversion function
    • M for multiply
    • D for Divide
• If there is no match, look for a match to the sub conversion unit2
  • Repeat steps from sub conversion unit above

The formula I am using is in column F below:

Excel 2010
	A	B	C	D	E	F
1	Item Code	Item Description	UOM Pricing	Qty Shipped	Stock UM	Sales Stock UM
2	0100	Widget 1	CTR	75,232	CTR	75,232
Sales Data

Cell Formulas
Range	Formula
E2	=VLOOKUP($A2,Item_Master!$A$2:$K$936,COLUMN(Item_Master!C$1),0)
F2	=IF($C2=VLOOKUP($A2,Item_Master,COLUMN(Item_Master!C$1),0),$D2,IF($C2=VLOOKUP($A2,Item_Master,COLUMN(Item_Master!D$1),0),CHOOSE(VLOOKUP(VLOOKUP($A2,Item_Master,COLUMN(Item_Master!E$1),0),{"M",1;"D",2},2,0),$D2*VLOOKUP($A2,Item_Master,COLUMN(Item_Master!F$1),0),$D2/VLOOKUP($A2,Item_Master,COLUMN(Item_Master!F$1),0)),IF($C2=VLOOKUP($A2,Item_Master,COLUMN(Item_Master!G$1),0),CHOOSE(VLOOKUP(VLOOKUP($A2,Item_Master,COLUMN(Item_Master!H$1),0),{"M",1;"D",2},2,0),$D2*VLOOKUP($A2,Item_Master,COLUMN(Item_Master!I$1),0),$D2/VLOOKUP($A2,Item_Master,COLUMN(Item_Master!I$1),0)))))

This formula works fine, but it's a bit complex, and I am hoping someone here can help me to simplify it.

Thanks very much.

 

[Peter_SSs]

Ben

It seems to me that you have a named range "Item_Master" being (at least) all the data rows and columns A:I on the 'Item_Master' sheet.

In that case, try these formulas on the 'Sales Data' worksheet.

E2:

=VLOOKUP($A2,Item_Master,3,0)

F2:

=IF(C2=E2,D2,IF(VLOOKUP(A2,Item_Master,4,0)=C2,D2*(VLOOKUP(A2,Item_Master,6,0))^(1-2*(VLOOKUP(A2,Item_Master,5,0)="D")),D2*(VLOOKUP(A2,Item_Master,9,0))^(1-2*(VLOOKUP(A2,Item_Master,8,0)="D"))))

 

[bschwartz]

That's totally brilliant, thank you!!

And yes, that table is named Item_Master. I apologize; I neglected to mention that.

 

[Peter_SSs]

Glad it worked for you. Thanks for letting us know.