use If formula to match unit of measure and then multiply or divide

[jnsturze]

So here is my issue. i have items that our system looks at in basic units i.e. 100 bandaids in a box, 10 boxes in a case, etc.

what i need to do is find out a way to figure out how many onhands we have of a PURCHASE UNIT. not necessarily in basic units and then multiply that by the purchase unit. Below you will see 3 examples

as you can see 200 and 600 are my minimum and maximum pars. the basic unit and how many come in it. Unit 2 and how many basic units come in unit 2, etc.

What i want to do is figure our how many purchase units i have based on my basic unit. I.e. Device Blood transfer i have 200 and 600 basic, but it is purchased by the case and there are 200 per case. Therefore i have a minimum of 1 case and maximum of 3 cases. in this fashion i can convert onhands to purchase units as well. (667/200) = 3.3 cases


i have tried dividing this manually but everytime a row gets added or deleted it multiplies or divides the wrong qty. any help would be greatly appreciated
[TABLE="width: 999"]
<TBODY>[TR]
[TD]
desc</SPAN>[/TD]
[TD]min</SPAN>[/TD]
[TD]max</SPAN>[/TD]
[TD]basic unit</SPAN>[/TD]
[TD]basic qty</SPAN>[/TD]
[TD]unit 2</SPAN>[/TD]
[TD]unit 2 qty</SPAN>[/TD]
[TD]unit 3</SPAN>[/TD]
[TD]unit 3 qty</SPAN>[/TD]
[TD]onhands</SPAN>[/TD]
[TD="colspan: 2"]purchase unit</SPAN>[/TD]
[/TR]
[TR]
[TD]DEVICE BLOOD TRANSFER B-D 364880</SPAN>[/TD]
[TD="align: right"]200</SPAN>[/TD]
[TD="align: right"]600</SPAN>[/TD]
[TD]EA</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]BX</SPAN>[/TD]
[TD="align: right"]100</SPAN>[/TD]
[TD]CA</SPAN>[/TD]
[TD="align: right"]200</SPAN>[/TD]
[TD="align: right"]667</SPAN>[/TD]
[TD]CA</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SOL DEX 5% SOD CHL 225% 1000ML 20 MEQ</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[TD="align: right"]24</SPAN>[/TD]
[TD]EA</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]CA</SPAN>[/TD]
[TD="align: right"]12</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]21</SPAN>[/TD]
[TD]CA</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SOL IONOSOL MB DEX 5% 250ML 7372-02 24CA</SPAN>[/TD]
[TD="align: right"]24</SPAN>[/TD]
[TD="align: right"]96</SPAN>[/TD]
[TD]EA</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]CA</SPAN>[/TD]
[TD="align: right"]24</SPAN>[/TD]
[TD][/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]98</SPAN>[/TD]
[TD]CA</SPAN>[/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=11></COLGROUP>[/TABLE]

 

[Marcelo Branco]

Hi,

Not sure if i understand what you need. Let me ask:
Considering your data sample are 3.34, 1.75 and 4.08 the expected results?

M.

 

[jnsturze]

Correct. I need it adjustable because it may not always use the second unit, but third or 4th unit

 

[Marcelo Branco]

Assuming your data sample in A1:K4 try this in L2

=J2/INDEX($D2:$I2,MATCH(K2,$D2:$I2,0)+1)

M.

 

[Marcelo Branco]

Please, note: i edited the formula above

M.