Log In
Register
Home
November 30, 2021
Offline
I’ve done a lot of googling but can’t seem to find a good (simple) answer for my problem. I have a value which I am trying to evenly spread across 7 cells, and then round up or down to the nearest 0.5.
For example I’m trying to divide a number into even doses of a medication - 23.5 total mg spread (mostly) evenly into 7 days, then rounded to the nearest 0.5 mg
so if 23.5 is my number, Monday would be 4mg, Tuesday 4mg, Wednesday etc etc.
hopefully this makes sense! I’ve included a picture for reference. Thanks in advance !!
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Daniel
You can try
=ROUNDUP($A$2/7,0)
You should attach more samples of your expected results (preferably in a workbook) for us to give a better suggestion.
BTW there is no picture attached.
Hope this helps.
Sunny
November 30, 2021
Offline
Sunny,
I will try to upload my file later today!
November 30, 2021
Offline
https://docs.google.com/spread.....1672722290
Here is the link to the google docs sheet. Hopefully this works, if there's better way to upload just the xls file, I'm not sure of it!
If you look at row 3 I've started trying to figure it out. I feel like that almost works, but I need to be able to round my numbers to the nearest 0.5mg. Each day of the week is calculated off of the number from D3. I hope this is more clear! (as a side note - row 15 was more of me trying to figure different things out, so you can ignore that row =) )
Thanks in advance!
Daniel
(ps: I think I uploaded the xls file)
VIP
Trusted Members
June 25, 2016
Offline
Hi Daniel
You should provide as many scenarios as possible (preferable 10).
It is difficult for us to guess your expected results for different values.
Please refer attachment for what I "guess" is what you wanted.
Good luck.
Sunny
November 30, 2021
Offline
Sunny,
That is exactly what I'm after, except I need to be able to round to the nearest 0.5mg for each day. So if my weekly dose is 30.5mg My days might look like 4, 4, 4, 4, 5, 5, 4.5. Or even if I have multiple days that are 4.5,4.5, 5, 5, 5, etc etc that would be fine too.
Basically the medication comes in 1mg, 2mg, 2.5mg, 3mg 4mg, 5mg 6mg, 7.5mg, and 10mg tablets, so If I could get my results to come in factors of that it would be ideal, but that may be asking too much haha!
I hope that clears it up some?
-Daniel
VIP
Trusted Members
June 25, 2016
Offline
Hi Daniel
This is as close as I can get to what you wanted.
As for your factors, I don't think it is too much to ask. Many things are possible in Excel.
It is just that no one (me included) figured out the formula just yet 
Good luck.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Hi Daniel
I have this crazy idea of using VLOOKUP to get close to the factors you wanted.
It doesn't work on all figures though 
Hope it helps.
Sunny
Answers Post
November 30, 2021
Offline
Sunny,
You're absolutely brilliant!! I see it doesn't work for Sunday, but that's ok. The rest is amazing! Thank you so much! Its perfect!
- Daniel
VIP
Trusted Members
December 7, 2016
Offline
Hello,
I have added another solution using IF and MROUND functions. See attached file, sheet IF solution. The numbers adds up nicely.
Br,
Anders
November 30, 2021
Offline
Anders,
I’ll check it out when I get home, thanks for the reply!
I know I’ve made this difficult enough as it is, but I wonder if you guys know of anyway to select which variables it gives answers in?
for example, if the patient has 2.5’mg tabs and 5mg tablets at home, can I select those as options for my results to display?So that the results only show in factors of 2.5 or 5? Or 4 and 7.5 if that’s the pill size I select … that may be too complex for excel though 
lol
VIP
Trusted Members
June 25, 2016
Offline
Hi Daniel
Like I had mentioned before, give us examples of your data and the expected results, as many as possible, so that we can see many scenarios to get a better understanding of your needs.
You mentioned my solution did not work for Sunday. The Sunday figure is the balancing amount for the total weekly dosage. So what is the expected result from the examples I gave you?
Do provide examples for your latest factors request.
Sunny
November 30, 2021
Offline
Sunny,
Your result still worked out well. That’s exactly what I was trying to do. That left over day still worked out well.
I’ll try to explain a little better about the latest request
Patients often have two prescriptions of Coumadin at home that vary from the doses I provided before (1mg, 2mg, 2.5mg, 3mg 4mg, 5mg 6mg, 7.5mg, and 10mg tablets). So some patients may have a 2mg prescription and 4mg, others may have a 3mg and 2.5mg, or a 7.5mg and 2mg. And when we adjust our INR levels we give suggestions on how to increase or decrease their dosage based off of those recommendations. Some we may say “on Monday/wed/Friday take 2 of your 2mg pills (to equal 4mg) and on tue, Thur, sat,sun you take your 5mg tablet (which will equal the total weekly mg dosage even though there may be a little left over). But, rather than call in a new prescription every week for whatever new mg they need, It would be neat if we could somehow select which dosage of medications they already have at home, and then the results display in factors of those dosages.
the 2mg tablet can be broken in half, and the 5mg tablet can be broken in half.
So if I could somehow select “2mg and 5mg tablets “ is what they take (or whatever combo of two strengths). Then results would display in factors of 1,2, 2.5, or 5. Or if they take a 3mg and 4mg. Results would display in factors of 1.5mg (1/2 of 3) 3mg, 2 (1/2 of 4) and 4mg
But that’s asking a lot. And is probably a pretty bulky request. The formula you provided previously still works super well, I’m just trying to make it easier so I can basically just do “brain off” adjustments
November 30, 2021
Offline
I suppose I could use the vlookup technique you previously used, Sunny. And they could enter their mg dosage. Then I could just narrow my vlookup to just two cells?
VIP
Trusted Members
June 25, 2016
Offline
I tested that too but without any examples I was unable to determine it's accuracy.
You can give it a try and let us know how it worked out.
November 30, 2021
Offline
I just tested the vlookup method and narrowed my vlookup range to 2 cells and tried a few numbers. Doesn't seem as effective, it will give me something likernIf I have 29 as my weekly dosage and I use 2mg and 4mg as my Vlookup integers my results end up looking like this:rnMon - 2, Tue - 2, Wed - 2, Thur - 2, Fri 4, Sat - 4, Sun -9rn rnSo it clumps all of my leftovers at the end of the week rather than spread that extra 9mg out like this: Monday -4, Tue 4, Wed - 2, Thur -2, Friday -4, Sat - 4, Sun - 1 (1/2 of a 2mg tab) . So I guess for now ill stick with your original suggestion!
VIP
Trusted Members
June 25, 2016
Offline
Hi Daniel
I have modified my original formulas.
I think it will work for any combination of dosages.
I have included 3 examples.
Do let us know your test results.
Good luck.
Sunny
1 Guest(s)
