Excel formula to calculate numbers into boxes and bottles

[RIchavalues]

Hi Experts,

Hope you are all going good and stay safe.
I have excel data as below:

+-------------+-----+--------+-----+--------------+
| 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

=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:

=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

 

[RIchavalues]

Hi All,

I really appreciate all your help and time on providing formula. My bad that I didn't mention excel version in OP.
Special Thanks to Fluff.

Hope you all stay safe.

Thanks,
Richa

 

[Fluff]

Glad we could help & thanks for the feedback.