+ Reply to Thread
Results 1 to 8 of 8

Formula needed to split totals into 2 different cells based on percentage

  1. #1
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Formula needed to split totals into 2 different cells based on percentage

    A1 = amount entered EXP: There is an existing formula for A1 that generates an amount based on other cells. For the purpose of this formula, I simply need an add on formula to calculate the following once this number is already generated from the existing formula. Lets say the number generated from the existing formula = 100
    B1 = percentage entered EXP: 25%
    Worksheet!C1 = the remaining percentage difference from what was entered into B1 * A1. In this case B1=25% so Worksheet!C1 answer should be 75% * 100 = 75 and A1 should now = 25.

    2nd Example:
    If the formula answer for A1 is 500 based on the formula prior, then when I enter 60% into B1,
    A1 & Worksheet!C1 should populate the follow:
    A1 = 300
    B1=60%
    Worksheet!C1 = 200

  2. #2
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    570

    Re: Formula needed to split totals into 2 different cells based on percentage

    Slight problem with this if I've read correctly.

    A1=100 B1=25% C1 = (1-B1)*A1

    I think this is what you are trying to do? C1 will show 75 and then you want to change A1 to equal C1. Doing this will create a circular reference (C1 will need a value in A1 to use and A1 will need a value in C1).

    So instead of trying to get the result back into A1 can you use the result in C1 for any futher use?
    Last edited by Harribone; 07-07-2013 at 02:38 PM.
    Say thanks, click *

  3. #3
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Re: Formula needed to split totals into 2 different cells based on percentage

    Currently the amount in A1 populates based on an existing formula. Currently I added *B1 to the end of the formula and it will give the % I enter times the amount generated from the existing formula. So if my existing formula produced an answer in A1 of 700 and I entered 20% into B1 then A1 will show 140 (20% * 700)

    All I need to do know is calculate the difference into C1 which in this case would = 560 (80% * A1's original answer of 700)

    It works all the way up to this last step of the C1 calculation.

  4. #4
    Registered User
    Join Date
    07-07-2013
    Location
    Dubai
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Formula needed to split totals into 2 different cells based on percentage

    aa 100.00
    ee 200

    Total 300.00 (=sum(aa+ee)

    % 60%

    Total1 180.00 ( =total*% )

    Total2 120.00 ( =300-180 )

    I hope the above illustration explain the formula
    Attached Images Attached Images

  5. #5
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Re: Formula needed to split totals into 2 different cells based on percentage

    I am not following

    Currently here is the exact formula I am using.
    =IF(M13="",H13*L13,(MIN(H13,100000)*L13)+(MAX(0,H13-100000)*M13))

    This formula is calculating another set of values based on input.

    N13 = where the formula sits
    K13 = percentage I want to enter
    Dashboard!35 = Where I want to populate the difference in percentage entered into K13 * the original answer given in N13.

    I adjusted it slightly to multiply the answer given in original formula * K13 which looks like this:

    =IF(M13="",H13*L13,(MIN(H13,100000)*L13)+(MAX(0,H13-100000)*M13))*K13

    If I entered 30% into K13, and the original formula total for N13 was 100 then N13 should show 30.
    I just need Dashboard!35 to now populate the balance which would be 70% * 100 showing 70 in Dashboard!35.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula needed to split totals into 2 different cells based on percentage

    Maybe Im missing something here, but would this work for you...

    =your-formula*B1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    07-06-2013
    Location
    USA
    MS-Off Ver
    MS Office 2019 Pro Plus
    Posts
    182

    Re: Formula needed to split totals into 2 different cells based on percentage

    Please take a look at the attached worksheet which I laid out in a simple format.

    G2 is set to multiply C2 (Sales Price) by D2 (Total Commission) * E2 (commission 1 payout)
    From this point I need the remaining balance of the commission to go to F@ (comm. 2)
    So the original commission amount was C2 * D2 which = $10,000
    In the formula we have calculated E2 * the overall commission of $10,000 which gave us $4,000
    The remaining amount left would be $6,000 which needs to go into F2. That is what I am struggling with. Having excel calculate the remaining percentage, whatever it may be, * the overall commission which in this case was $10,000.
    Attached Files Attached Files

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula needed to split totals into 2 different cells based on percentage

    will this give you what want?

    =C2*D2*(1-E2)

+ 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