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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
What version of Excel are you using?
Please update your account details to show this, as it affects what functions you have.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top