Options

# Excel Function Help

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!

## Posts

• Options
Registered User regular
Is it for a whole sheet of differing values or just once at a time, if the latter then the excel solver might be of use.

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?

• Options
Registered User regular
Define "efficiency."

Are you looking for the fewest number of vials, the cheapest price, the least amount of overage, something else entirely?

• Options
resting shark face Registered User regular
I've spent too much time thinking about this already, but there is a pattern, I just can't think of a neat mathematical equation for it yet. But anyways, I'll just post what I have here and maybe someone else will finish what I started:

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!

Please consider the environment before printing this post.
• Options
resting shark face Registered User regular
You can also brute force this by creating a table with the above pattern and doing VLOOKUP

Please consider the environment before printing this post.
• Options
No Pic EverRegistered 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!

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.

• Options
San DiegoRegistered User regular
In case you want the horrifically inelegant, crazy long IF statement you mentioned, I think these will work:

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

• Options
Registered User regular
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.

• Options
Registered User regular
Elvenshae wrote: »
Define "efficiency."

Are you looking for the fewest number of vials, the cheapest price, the least amount of overage, something else entirely?

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!

• Options
Registered User regular
schuss wrote: »
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.

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

• Options
Registered User regular
Dis' wrote: »
schuss wrote: »
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.

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.

• Options
Registered User regular
Actually, I think the right answer here is that you only need to solve for order quantities of 1-199.

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:
• 1x100ml +
• Optimal answer for 102ml, 3x40ml

360ml?
• 2x100ml +
• Optimal answer for 160ml, 4x40ml

363ml?
• 2x100ml +
• Optimal answer for 163ml, 1x100ml + 2x40ml

321ml?
• 2x100ml +
• Optimal answer for 121ml, 1x100ml + 1x40ml

Thoughts?

• Options
Registered User regular
Excel's solver will do it.

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.

• Options
Registered User regular
edited July 2016
You can use SUMIF to sort the numbers easily using a 3x3 table. I'll use A = 100 and B = 40.

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.

Kipling on
3DS Friends: 1693-1781-7023
• Options
Registered User regular
edited July 2016
If you don't mind having an extra column with the total amount dispensed, I think I have an efficient solution with no need for tables, macros, or solver.

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)

MrTLicious on