+ Reply to Thread
Results 1 to 10 of 10

Multiply by percentage and round final results

  1. #1
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Multiply by percentage and round final results

    This could be a little hard to explain, but I'll try and if needed, attached is a sample.

    The number in D2 represents number of employees.

    In D4/D5 the number of employees is multiplied by the corresponding percentage to get the number of employees within that section. Section 1 has employees A and B.

    In E4/E5 I need a whole number for that group of employees, but within each section, any residual would fall to the second group (so any default would roll to employees B).

    For scenario 1, in the end, the total number of employees would need to be 313.

    Is there a good way to do this?
    Attached Files Attached Files
    HTH
    Regards, Jeff

  2. #2
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Multiply by percentage and round final results

    Hi Jeff,

    Use the below formula in cell E4 and copy it into E5, E7, E8 and E10.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The final value in E12 (total) will be 313 (as you had requested).

    Hope this helps.
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.

  3. #3
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Multiply by percentage and round final results

    Quote Originally Posted by jeffreybrown View Post
    In E4/E5 I need a whole number for that group of employees, but within each section, any residual would fall to the second group (so any default would roll to employees B).
    Hi kbkumar,

    This would not give me a whole number. For Section 1, employee B needs to be a whole number, so 23.5 will not work.

  4. #4
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Multiply by percentage and round final results

    Sorry, did not read the OP carefully with regards to the Whole number criteria...

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Multiply by percentage and round final results

    where does the residual of the initial value 'D2' wind up?
    I can get to 112 as the final answer, but maybe rounding up the initial value to next whole number will work..

    in scenario 2, the round ups outnumber the round downs so it adds the extra whole number, while in scenario 1 the reverse situation occurs,not sure if that will help you..
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Multiply by percentage and round final results

    The initial value, D2, will always roundup to a whole number. I can easily roundup the initial value but what about the levels in-between, they need to be whole numbers?

  7. #7
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Multiply by percentage and round final results

    Hi Jeff,

    Have a look at the attached, which I hope will work for you. I've added a formula in column F to ask for the "remainder" if the number in E has been rounded down. This "remainder" is added to the next number before it is rounded, resulting in a total of 313 in E12.
    Attached Files Attached Files
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.

  8. #8
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Multiply by percentage and round final results

    okay, I will upload what i tried, have put some comments beside what i see happening, not really sure how to change the situation to get 313 as the final answer..

    maybe if there is a residual, add 1 instead?
    Attached Files Attached Files
    Last edited by dredwolf; 11-04-2012 at 05:41 PM.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Multiply by percentage and round final results

    Thanks guys...this gives me some things to play with.

  10. #10
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Multiply by percentage and round final results

    please let me know what you finally come up with, it is an interesting problem

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1