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