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

1. ## 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

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

I have attached an example.

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

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

4. ## 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.

5. ## 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. ## Re: How can I add a percentage modifier to a Total?

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

7. ## 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.``

8. ## 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

9. ## 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. ## 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. ## 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

12. ## 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?

Originally Posted by MrShorty
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. ## Re: How can I add a percentage modifier to a Total?

Presumbaly

Formula:
`Please Login or Register  to view this content.`

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

Originally Posted by Richard Buttrey
Presumbaly

Formula:
`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

#### Thread Information

##### Users Browsing this Thread

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

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