+ Reply to Thread
Results 1 to 14 of 14

How can I add a percentage modifier to a Total?

  1. #1
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Question How can I add a percentage modifier to a Total?

    Hi,

    Let's say Column A has a list of numbers and at the bottom, there is a Total eg. A11 =SUM(A1:A10)
    Now, somehow I want to add a percentage modifier to the Total. I can put this another cell eg. A12 =IF(A3>2,A11*1.066)

    But... Is there a way that I can modify the Total ie. A11
    I don't want multiple running totals for every modifier. My actual sheet is several hundred rows.
    Every way I try it I get a Circular Equation error.

    Thankyou
    Last edited by 3vangelica; 03-10-2021 at 06:44 AM.

  2. #2
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    I have attached an example.
    Attached Files Attached Files

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,114

    Re: How can I add a percentage modifier to a Total?

    Perhaps with A11: =SUM(A1:A10)*IF(A3>2,1.066,1)
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: How can I add a percentage modifier to a Total?

    Probably need a more complete example to really address all the issues.

    For the specific example given, I would have expected something like =SUM(A1:A10)*IF(A2>2,1.066,1).

    Your sheet talks about needing to this for multiple rows and columns, but, with only 1 column of data, it is difficult for me to visualize exactly what you mean. You also talk about needing multiple modifiers, but you give no examples with multiple modifiers. So, that's my simple solution for the simple example given, but I don't know how well that will scale up to your actual problem.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    Sorry, I was trying to keep the example small as the Sample Workbook banner suggests. I will upload the actual workbook (there's nothing personal in there) tomorrow.

    Thankyou for your responses

  6. #6
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    Ok, not the full workbook but an adequate example, I think.
    Attached Files Attached Files

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: How can I add a percentage modifier to a Total?

    It is better for helpers, if you manual input the expected outcome
    Not sure I understand 100%, but try in K3:
    Please Login or Register  to view this content.
    Drag down and accross

    Upper formula is for minus case, i.e, 3 & 20% = 3-20%*3=80%*3
    If you want it to be: 3+20%=120%*3:

    Please Login or Register  to view this content.
    Last edited by bebo021999; 03-08-2021 at 11:49 PM.

  8. #8
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    Hello again,

    Ok, I've uploaded here an updated version of my example (hopefully) explaining better what I need to do. It also includes a second sheet detailing my 'solution' but my solution is not really workable because it prohibits the ability to add further modifiers in the future without re-routing the entire equation chain. See what you think? Let me know if there is an easier solution.

    Thankyou
    Attached Files Attached Files

  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,114

    Re: How can I add a percentage modifier to a Total?

    ...I can only see one sheet.

    You need to explain which columns/cells we are supposed to be working with and which are the results cells. In addition manually add results and explain what calculations you have made to obtain the results.

    You'll also need to explain what you mean by further modifiers and the effect these have and when.

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,363

    Re: How can I add a percentage modifier to a Total?

    It was a bit of a challenge to follow the sequence of calculations being done in the unworkable solution sheet (I found the arrangement in example table more difficult to work with, so I did not do anything on that sheet). To make sure I understand the sequence of calculations, you:

    1) multiply each value in B3:B15 by the multipliers in K19:K31, then add them all together to get the value in K35. The sum function in K35 could then be replaced with =SUMPRODUCT(B3:B15,K19:K31)
    2) Then you take "modifier" percentages, multiply them by the corresponding value in B3:B15, multiply that result by the value in K35 and add it to K35. In a more algebraic notation newresult=K35+K35*B12*0.2+K35*B14*0.075. If I put my J, K, L, M modifiers in K51:K54 (so K51=0.2, K52=0, K53=0.075, K54=0), this simplifies to K35*(1+SUMPRODUCT(B12:B15,K51:K54)). I can now enter =K35*(1+SUMPRODUCT(B12:B15,K51:K54)) into K1. Or, if I wanted to eliminate K35, I could enter =SUMPRODUCT(B3:B15,K19:K31)*SUM(1,SUMPRODUCT(B12:B15,K51:K54)) into K1

    The reason I didn't try this on the example table is because you have embedded the modifiers among the multipliers, which makes it difficult to use the SUMPRODUCT() function. If you will rearrange example table so that the j, k, l, m modifiers are in a separate range from the j, k, l, m multipliers, then you can write a similar formula in example table.

    Does that help at all?

  11. #11
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    Mr Shorty...

    WOW!

    Thankyou, Thankyou, Thankyou.

    You did really well to understand what I was trying to achieve. I was finding it really difficult to explain.

    But yes, that is it. Thankyou. :D
    Last edited by 3vangelica; 03-10-2021 at 05:08 AM.

  12. #12
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    I've just realised there is one small problem. I only want to add the percentage modifiers if the total is greater than 0.
    How can I add an IF formula into this?

    Quote Originally Posted by MrShorty View Post
    Or, if I wanted to eliminate K35, I could enter =SUMPRODUCT(B3:B15,K19:K31)*SUM(1,SUMPRODUCT(B12:B15,K51:K54)) into K1
    As in: IF =SUMPRODUCT(B3:B15,K19:K31) is greater than 0 then *SUM(1,SUMPRODUCT(B12:B15,K51:K54))

    Thankyou

  13. #13
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,114

    Re: How can I add a percentage modifier to a Total?

    Presumbaly

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

  14. #14
    Registered User
    Join Date
    02-15-2018
    Location
    Brighton, England
    MS-Off Ver
    Microsoft Excel 2019 MSO (16.0.13628.20128) 32-bit
    Posts
    14

    Re: How can I add a percentage modifier to a Total?

    Quote Originally Posted by Richard Buttrey View Post
    Presumbaly

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thankyou for helping me understand how to construct the formula. I was unsure how/where to add the IF.

    The actual formula I was looking for is: =IF(SUMPRODUCT(B3:B15,K19:K31)>0,SUMPRODUCT(B3:B15,K19:K31)*SUM(1,SUMPRODUCT(B12:B15,K51:K54)),SUMPRODUCT(B3:B15,K19:K31))

    But I wouldn't have got there without your help so, Thanks for that
    Last edited by 3vangelica; 03-10-2021 at 06:43 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Adding percentage columns and deleting its Total but not Sum Total
    By Danak in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-31-2020, 04:31 AM
  2. Comparing a percentage of a total to the total in pivot table and chart
    By BillDoyle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-27-2018, 03:56 PM
  3. [SOLVED] Multiplying Total by Percentage then Summing up Result to Equal Original Total
    By adopotato in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-25-2017, 11:48 AM
  4. Replies: 2
    Last Post: 07-19-2017, 04:41 PM
  5. PivotTable sub-total/grand total percentage issue
    By Jess0121 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-19-2016, 05:59 PM
  6. Getting sum total from percentage instead of percentage amount
    By Bluedemon909 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2013, 08:56 AM
  7. Modifier Grand Total Calcuation in Pivot Table
    By dssrun209 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-19-2011, 05:01 PM

Tags for this Thread

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