• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Dividing integer across multiple cells evenly|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Dividing integer across multiple cells evenly|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Dividing integer across multiple ce…
sp_PrintTopic sp_TopicIcon
Dividing integer across multiple cells evenly
Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
1
November 30, 2021 - 10:20 am
sp_Permalink sp_Print

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

sp_AnswersTopicSeeAnswer See Answer
Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
2
November 30, 2021 - 9:50 pm
sp_Permalink sp_Print

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

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
3
November 30, 2021 - 9:53 pm
sp_Permalink sp_Print

Sunny,

I will try to upload my file later today!

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
4
November 30, 2021 - 10:02 pm
sp_Permalink sp_Print

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)

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
5
December 1, 2021 - 2:08 am
sp_Permalink sp_Print

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

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
6
December 1, 2021 - 2:35 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
December 1, 2021 - 5:45 pm
sp_Permalink sp_Print

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 Laugh

Good luck.

Sunny

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
December 1, 2021 - 6:23 pm
sp_Permalink sp_Print

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 Frown

Hope it helps.

Sunny

sp_AnswersTopicAnswer
Answers Post
Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
9
December 1, 2021 - 10:50 pm
sp_Permalink sp_Print

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

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 873
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
10
December 2, 2021 - 9:13 am
sp_Permalink sp_Print

Hello,

I have added another solution using IF and MROUND functions. See attached file, sheet IF solution. The numbers adds up nicely.

Br,
Anders

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
11
December 2, 2021 - 9:57 am
sp_Permalink sp_Print

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 Kiss lol

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
12
December 2, 2021 - 4:49 pm
sp_Permalink sp_Print

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

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
13
December 2, 2021 - 9:07 pm
sp_Permalink sp_Print

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 Laugh

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

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
14
December 2, 2021 - 9:36 pm
sp_Permalink sp_Print sp_EditHistory

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?

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
15
December 2, 2021 - 11:29 pm
sp_Permalink sp_Print

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.

Avatar
Daniel M
Member
Members
Level 0
Forum Posts: 9
Member Since:
November 30, 2021
sp_UserOfflineSmall Offline
16
December 3, 2021 - 12:00 am
sp_Permalink sp_Print

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!

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
17
December 3, 2021 - 6:15 pm
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ashleigh Farquharson
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Gilbert Lemek
Ashleigh Farquharson
Jayz Luu
Fred Smith
Charles DeGraffenreaid
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Forum Stats:
Groups: 3
Forums: 24
Topics: 6360
Posts: 27812

 

Member Stats:
Guest Posters: 49
Members: 32340
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.