RIchavalues
New Member
 Joined
 Feb 24, 2016
 Messages
 25
 Office Version

 2019
 Platform

 Windows
Hi Experts,
Hope you are all going good and stay safe.
I have excel data as below:
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
and for bottle:
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
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