Excel formula to calculate numbers into boxes and bottles

RIchavalues

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
Hi Experts,

Hope you are all going good and stay safe.
I have excel data as below:
Code:
+-------------+-----+--------+-----+--------------+
| Item        | Box | Bottle | ml  | Open Balance |
+-------------+-----+--------+-----+--------------+
| Jim Beam    |     |        | 750 | 23           |
+-------------+-----+--------+-----+--------------+
| Jim Beam    |     |        | 375 | 24           |
+-------------+-----+--------+-----+--------------+
| Crown Apple |     |        | 180 | 100          |
+-------------+-----+--------+-----+--------------+

If "ml" is 750, then box will contain 12 bottles. Similarly if ml is 375, box will contain 24 bottles and if ml is 180 then box will contain 48 bottles.
Based on "Open Balance" column, am trying to calculate "Box" and "Bottle" column. For row 1, box should be 1 and Bottle should be 11. For Row 2, Box should be 1 and bottles will be 0 and for row 3, box will be 2 and bottles will be 4.

I tried below formula for box
VBA Code:
=IF(OR(I4=750,I4=650),INT(L4/12),IF(OR(I4=500,I4=375,I4=330,I4=275),INT(L4/24),IF(I4=180,INT(L4/48),IF(I4=90,INT(L4/96),IF(I4=60,INT(L4/150),"")))))

and for bottle:
Code:
=IF(AND(OR(I4=750,I4=650),L4<12),L4,IF(AND(OR(I4=500,I4=375,I4=330,I4=275),L4<24),L4,IF(AND(I4=180,L4<48),L4,IF(AND(I4=90,L4<96),L4,IF(AND(I4=60,L4<150),L4,"")))))

Am not getting correct results. Hence reaching out for help. Please let me know if there's any other methods or easier formula to get correct results. Appreciate your help and time.
Thanks,
Richa
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,639
I put the ml/count values in a table of its own in G1:H4 to make things easier. It could be included in the formulas, but that would complicate things.

Book2
ABCDEFGH
1ItemBoxBottlemlOpen BalancemlCount
2Jim Beam1117502375012
3Jim Beam103752437524
4Crown Apple2418010018048
Sheet6
Cell Formulas
RangeFormula
B2:B4B2=INT(E2/VLOOKUP(D2,$G$2:$H$4,2,0))
C2:C4C2=MOD(E2,VLOOKUP(D2,$G$2:$H$4,2,0))


See if this works for you.
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
226
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
A
B
C
D
E
ItemBOXBOTTLEmlOpen balance
Jim Beam
1​
11​
750​
23​
Jim Beam
1​
0​
375​
24​
Crown Apple
2​
4​
180​
100​


A
B
C
D
E
ItemBOXBOTTLEmlOpen balance
Jim Beam
=INT(E2/VLOOKUP(D2,{750,12;375,24;180,48},2,0))​
=MOD(E2,VLOOKUP(D2,{750,12;375,24;180,48},2,0))​
750​
23​
Jim Beam
=INT(E3/VLOOKUP(D3,{750,12;375,24;180,48},2,0))​
=MOD(E3,VLOOKUP(D3,{750,12;375,24;180,48},2,0))​
375​
24​
Crown Apple
=INT(E4/VLOOKUP(D4,{750,12;375,24;180,48},2,0))​
=MOD(E4,VLOOKUP(D4,{750,12;375,24;180,48},2,0))​
180​
100​

EDIT: Like EricW without separate packrate table.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Two options dependant on version
+Fluff New.xlsm
ABCDE
1ItemBoxBottlemlOpen
211175023
31037524
424180100
5
611175023
71037524
824180100
Data
Cell Formulas
RangeFormula
B2:B4B2=INT(E2/LOOKUP(D2,{180,375,750},{48,24,12}))
C2:C4C2=MOD(E2,LOOKUP(D2,{180,375,750},{48,24,12}))
B6:B8B6=INT(E2/SWITCH(D6,180,48,375,24,750,12))
C6:C8C6=MOD(E2,SWITCH(D6,180,48,375,24,750,12))
 

RIchavalues

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have.
Thanks Fluff. Sorry about that. I was not knowing I could do that. I have updated my account details.
Am using Excel 2019 version.

Thank you
 

RIchavalues

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I put the ml/count values in a table of its own in G1:H4 to make things easier. It could be included in the formulas, but that would complicate things.

Book2
ABCDEFGH
1ItemBoxBottlemlOpen BalancemlCount
2Jim Beam1117502375012
3Jim Beam103752437524
4Crown Apple2418010018048
Sheet6
Cell Formulas
RangeFormula
B2:B4B2=INT(E2/VLOOKUP(D2,$G$2:$H$4,2,0))
C2:C4C2=MOD(E2,VLOOKUP(D2,$G$2:$H$4,2,0))


See if this works for you.
Hi Eric,

Thanks for quick response. I really appreciate your help.
Since this workbook is filled with numbers, I do not want to add extra column showing count of bottles in separate column as it will increase work and confuses.

Am looking for something which makes uses of existing columns and to enter count values in formula itself. Hope it helps. Thank you.

Thanks,
Richa
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
Thanks Fluff. Sorry about that. I was not knowing I could do that. I have updated my account details.
Thanks for that.
As you have 2019 both sets for formulas from post#5 should work.
 

RIchavalues

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
A
B
C
D
E
ItemBOXBOTTLEmlOpen balance
Jim Beam
1​
11​
750​
23​
Jim Beam
1​
0​
375​
24​
Crown Apple
2​
4​
180​
100​


A
B
C
D
E
ItemBOXBOTTLEmlOpen balance
Jim Beam
=INT(E2/VLOOKUP(D2,{750,12;375,24;180,48},2,0))​
=MOD(E2,VLOOKUP(D2,{750,12;375,24;180,48},2,0))​
750​
23​
Jim Beam
=INT(E3/VLOOKUP(D3,{750,12;375,24;180,48},2,0))​
=MOD(E3,VLOOKUP(D3,{750,12;375,24;180,48},2,0))​
375​
24​
Crown Apple
=INT(E4/VLOOKUP(D4,{750,12;375,24;180,48},2,0))​
=MOD(E4,VLOOKUP(D4,{750,12;375,24;180,48},2,0))​
180​
100​

EDIT: Like EricW without separate packrate table.
Thanks KOKOSEK. Appreciate your response. Am getting error. Not sure which version of excel works. Sorry.

Thanks,
Richa
 

RIchavalues

New Member
Joined
Feb 24, 2016
Messages
25
Office Version
  1. 2019
Platform
  1. Windows
Two options dependant on version
+Fluff New.xlsm
ABCDE
1ItemBoxBottlemlOpen
211175023
31037524
424180100
5
611175023
71037524
824180100
Data
Cell Formulas
RangeFormula
B2:B4B2=INT(E2/LOOKUP(D2,{180,375,750},{48,24,12}))
C2:C4C2=MOD(E2,LOOKUP(D2,{180,375,750},{48,24,12}))
B6:B8B6=INT(E2/SWITCH(D6,180,48,375,24,750,12))
C6:C8C6=MOD(E2,SWITCH(D6,180,48,375,24,750,12))
Hi Fluff,

Perfect. Working like a charm. Thank you so much for your help. Actually second method with SWITCH worked. Getting error with lookup.
Appreciate your help.

Thanks,
Richa
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top