Excel Function Help
TheBlackWind
Registered User regular
Registered User regular
I'm trying to devise a spreadsheet to order medication, and I'm not sure if I can do what I'm trying to do here.
The drug comes in vials of 100 and 40. Is there an easy way to figure out the most efficient way to order these?
I've been using:
=ROUNDDOWN(G5/100,0) for the 100s
and
=(ROUNDUP((MOD(G5,100)),-2)/40) to figure out the remainder for the 40s
But that fails if the number is 158 and I should be ordering 4 bottles of 40, for a total of 160. It tells me to order 1 100 and 2 40s for 180.
Is there an easy way to do this, or am I just going to have to write a crazy long IF statement that checks remainders?
Thanks!
The drug comes in vials of 100 and 40. Is there an easy way to figure out the most efficient way to order these?
I've been using:
=ROUNDDOWN(G5/100,0) for the 100s
and
=(ROUNDUP((MOD(G5,100)),-2)/40) to figure out the remainder for the 40s
But that fails if the number is 158 and I should be ordering 4 bottles of 40, for a total of 160. It tells me to order 1 100 and 2 40s for 180.
Is there an easy way to do this, or am I just going to have to write a crazy long IF statement that checks remainders?
Thanks!
PAD ID - 328,762,218
0
Posts
An IF statement based solution will be pretty long because 40 doesn't fit nicely into 100 and you'll be evaluating combinations of 40s and 100s.
However you could just pre-calculate and store the best solution for values 1-240 and use a vlookup to get them?
Are you looking for the fewest number of vials, the cheapest price, the least amount of overage, something else entirely?
Steam: Elvenshae // PSN: Elvenshae // WotC: Elvenshae
Wilds of Aladrion: [https://forums.penny-arcade.com/discussion/comment/43159014/#Comment_43159014]Ellandryn[/url]
The number of 40s you need changes every 20 pills. Ignoring the first 60 pills it goes like this
61-80: 2
81-100: 0
101-120: 3
121-140: 1
141-160: 4
and repeats forever
The number of 100s you need also changes every 20 pills but also increments by 1 every 100 pills, so it goes like this:
61-80: 0
81-100: 1
101-120: 0
121-140: 1
141-160: 0
Then the next set (covering 161-260 pills) will be: 1 2 1 2 1 and so on
Good luck!
You don't need crazy if statements, you just need to consider that there are several ways you could be fullfilling this order:
1) Round up to nearest 100, so 200 units with 42 wasted.
2) Round down to nearest 100, then round up for nearest 40. So 180 units with 22 wasted.
3) Round up to the nearest 40. So you would order 160 units and have 2 wasted.
Then you set the program to give the min waste amount for all 3.
If you start exceeding 200 units, add a function at the start of each chain to round down to the nearest 200, count that value twice (multiply by 2), and that's your starting point for the rest of the functions. You have to do this because otherwise your 40 and 100 sets can come up with the same waste amount (e.g. at 220, both would waste 20).
So for the first let's assume we're in cell H5 and moving out.:
H5=ROUDNUP(G5/100,0) This is the calculation
H8=H5*100-G5 This is the amount wasted
I5=ROUNDDOWN(G5/100,0) This is first part of the calculation
I6=ROUNDUP((G5-100*I5)/40,0) This is the second part of the calculation
I8=H5*100+H6*40-G5 This is the amount wasted
J6=ROUNDUP(G5/40,0) This is the calculation
J8=H5*40=G5 This is the amount wasted
I've maintained that row 5 is always the 100's, row 6 is always the 40's, and row 8 is always the waste.
Now, you can visually see which is the Minimum of the three for amount wasted, but we can automate this as well as follows:
First, set up a a new table as follows:
L5=H8
L6=I8
L7=K8
M5=H5
M6=I5
N6=I6
N7=J6
O5="100 UNITS ONLY"
O6="MIXED SIZES"
O7="40 UNITS ONLY"
Then we can write a function that will pull information from the above table as follows:
We're using cell L10 as the description, L11 as the number of 100 unit vials, and L12 as the number of 40 unit vials
L10=VLOOKUP(MIN(H8:J8),L5:O7,4)
L11=VLOOKUP(MIN(H8:J8),L5:O7,2)
L12=VLOOKUP(MIN(H8:J8),L5:O7,3)
I hope this helps.
40s: =IF(G5<1,0,IF(G5<41,1,IF(G5<81,2,IF(G5<101,0,IF(VALUE(RIGHT(G5,2))>80,0,IF(VALUE(RIGHT(G5,2))>60,2,IF(VALUE(RIGHT(G5,2))>40,4,IF(VALUE(RIGHT(G5,2))>20,1,3))))))))
100s:
=IF(G5<81,0,ROUNDDOWN(G5/100,0)+IF(VALUE(RIGHT(G5,2))>80,1,IF(VALUE(RIGHT(G5,2))>60,0,IF(VALUE(RIGHT(G5,2))>40,-1,IF(VALUE(RIGHT(G5,2))>20,0,IF(VALUE(RIGHT(G5,2))>0,-1,0))))))
1. =X/40
2. =X/100
3. =ROUNDUP(1, 0)-1
4. =ROUNDUP(2, 0)-2
5. =IF(3<4, 1&"40 bottles", 2&"100 Bottles")
This is taking the division, comparing it to the closest round and selecting the closest gap.
Was looking for least amount of overages specifically. All these answers really set me down the right path, and gave me some good stopgaps until I can make the dosage charts for VLOOKUP (Which I probably should have just committed to sooner)
Thanks so much for all the help everyone!
This won't give you the closest answer in some cases though - for 130 the best answer is 1x100+1x40 but this formula set will tell you to use 4x40. (It was my first port of call as well
Good catch. This was my five minute answer, so not ideal.
Everything over that, you just take out 100s until you get back into the 1-199 range, and go from there.
Like, for 202ml, the optimal answer is to get 220ml (1x100ml + 3x40ml), which is:
360ml?
363ml?
321ml?
Thoughts?
Steam: Elvenshae // PSN: Elvenshae // WotC: Elvenshae
Wilds of Aladrion: [https://forums.penny-arcade.com/discussion/comment/43159014/#Comment_43159014]Ellandryn[/url]
What you have is an equation like:
need + overage = 100*a + 40*b
You want to minimize overage, subject to the constraints that it is >= 0 and that a and b are integers.
So if you have an Excel sheet set up like the following:
A1 = a
B1 = b
C1 = need
D1 = 100*A1 + 40*B1 - C1
Then you can use solver like this:
Set the objective to D1
By Changing Variable Cells to A1:B1
Subject to the constraints:
D1>=0
A1 is integer
B1 is integer
Then you can fill in your needed value in C1 and run the solver and it will minimize your overage.
Row 1 would be the number of A vials needed
Row 2 would be the number of B vials needed
Row 3 would be the remainder of pills
The columns would be for your three choices
A+B | A only | B only
=SUMIF(Row3,MIN(Row3), Row1) would give you the correct amount of A vials needed
=SUMIF(Row3,MIN(Row3), Row2) would give you the number of B vials needed
and
=MIN(Row3) would give you the excess
For the mixed version, where G5 is the amount needed
A#: =ROUNDDOWN(G5/LCM(A,B),0)*(LCM(A,B)/A)
Bs: =ROUNDUP((G5-A#*A)/40,0)
You can replace A and B with any number in this version. LCM() is least common factor function in Excel - the cycle loops there. So you could have a 80 and 30 and the math should still work out in this case.
If Column G has the amount needed, get the amount you know you'll need in column H. This is simply rounding up to the nearest 20, with an exception for numbers under 80, which go up to the nearest 40.
=IF(G1<80,ROUNDUP(G1/40,0)*40,ROUNDUP(G1/20,0)*20)
In column I, you'll get the number of 40s you need. You'll never need more than 4 (if you wanted 5, you could just use 2 100s). You know how many 40s you need by how big the 10s column. The choose function lets you pick a number based on the value of an index.
=CHOOSE(MOD(H1,100)/20+1,0,3,1,4,2)
Then, to get the number of 100s, simply see what you need leftover
=(H1-40*I1)/100
Edit: You could do it without the extra column but it's a lot messier
In H (40s): =CHOOSE(MOD(IF(G1<80,ROUNDUP(G1/40,0)*40,ROUNDUP(G1/20,0)*20),100)/20+1,0,3,1,4,2)
In I (100s): =ROUND((G1-40*H1)/100,0)