# batching volumes / help with IF and or INT?? help?!!

#### vpcm24

##### New Member
I have a series of volumes of vaccine that I need to order regulary ranging from 40ml to 1750ml the vaccines come in bottles of 250ml, 100ml and 50ml.
The quantities are listed in column H3 to H36 and the bottle sizes are listed J2 to L2.
What formula can I put in so that it automatically calculates how many of each bottle I will require? Also taking into consideration that if the quantity is 40ml I will nedd a 50ml bottle or if 80ml I will need a 100ml bottle etc??

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### konew1

##### Well-known Member
Assuming J2 has the 250ml size
Put these into J3:L3 and copy down
j3=INT(\$H3/J\$2)
K3=INT((\$H3-\$J3*\$J\$2)/K\$2)
L3=ROUNDUP((\$H3-\$J3*\$J\$2-\$K3*K\$2)/L\$2,0)

You might need provide more info. This solution returns for 210ml returns 2 of 100ml plus 1 of 50ml.
I suspect you would rather get 1 of 250ml.

Last edited:

#### konew1

##### Well-known Member
This is better
j3=INT(ROUNDUP(\$H3/50,0)*50/J\$2)
K3=INT((ROUNDUP(\$H3/50,0)*50-\$J3*\$J\$2)/K\$2)
L3=ROUNDUP((ROUNDUP(\$H3/50,0)*50-\$J3*\$J\$2-\$K3*K\$2)/L\$2,0)

#### vpcm24

##### New Member
Wow - that's awesome - thanks SO much for your help, works perfectly! thank you

#### konew1

##### Well-known Member
Thanks for the feedback. I just re-read the formula and while it will work it is unnecessarily complex

L3=ROUNDUP((\$H3-\$J3*\$J\$2-\$K3*K\$2)/L\$2,0)
That is the one I gave you the first time for L3

Cheers

Replies
0
Views
197
Replies
3
Views
408
Replies
8
Views
226
Replies
4
Views
634
Replies
1
Views
705

1,191,386
Messages
5,986,315
Members
440,017
Latest member
vasanrajeswaran

### 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.

### Which adblocker are you using?

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

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