# Adding with percantages and more

#### Jamie mills

##### New Member
Morning

I'm stuck
I have a price list from a supplier and I am trying to add the following ...........
*25%+100*15%
I would like to add this to the cell and keep the answer in the same cell not sure if that is possible????

All the start figures are different and there is about 250 to change so i guess I can copy formulas?

If not I will add a new column to put the answer there I have tried for about an hour with no joy I thought I had done it at only point only to get a value error!!!

Hope this makes sense
Jamie

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### Makrini

##### Well-known Member
Best bet is a new column - you can then copy and paste it back over the original if you wish.

Unsure what you are trying to do...

is it? 25% of the cell value + 15% of 100?

if so - isn't it just 25% of the cell value plus 15?

=(A1*0.25)+15

?

#### Jamie mills

##### New Member
I am trying to....

Hope this makes sense
Jamie

#### XLXRider

##### Board Regular
Re: Adding with percentages and more

Hmmm... having worked with financials for years, I too have tried to create a macro that would do something like you want. (I bet there's a macro whiz that can, it just isn't me!) If not, you'll have to manually edit every cell. So what I do is put the formula in another column, since the problem when recording said macro is that the value in the cell you're editing becomes part of the macro!

If you have 25 in cell B3, for instance, just put this in cell C3 and drag 'er down:
=B3*0.25+100*0.15. You can take it a step further, and have the variables reside in their own cells so that when you change the variables you only need to make the change in that particular cell and it'll hit all the cells. Maybe put the 25% in cell A1, the 100 in B1, and the 15% in C1. Now make your formula in C3 this: =B3*\$A\$1+\$B\$1*\$C\$1. Now, when you drag the formula down, your original price cell will always change to reflect the correct one you want to apply your formula to (B3 will become B4, B5, etc.) and the other references will remain as they are. The \$ indicates an "absolute reference" that is, indicating that you always want to use the numbers in cells A1, B1, and C1 to change your other numbers (B3, B4, and so on) to suit.

HTH...
Regards,
XLXRider

OK, just saw your last post... make the formula parenthesized like this:=(B3*1.25+100)*1.15

Last edited:

#### Makrini

##### Well-known Member
actually a Macro for something like this is quite simple.

Code:
``````Sub changeit()
Dim endcell as long

endcell=Range("A64000").end(xlup)
For each Cell in Range("A2:A" & endcell)
Cell.Value = (Cell.Value*1.25+100)*1.15
Next Cell
End Sub``````

#### Jamie mills

##### New Member
Thanks
That sort of works the issue i am having is now...
£615*25%+100*15%=999.06
Excel says £168.75
not sure why any ideas?
Thanks
Jamie

#### Makrini

##### Well-known Member
Because Excel is correct....

£615*25%+100*15%

remember highschool math - multiplication will always happen before addition...

i.e 615*25% = 153.75
100*15% = 15
168.65+15 = 168.75

put brackets (parenthesis) in

#### Jamie mills

##### New Member
All coming back to me now.......
is there a way of rounding up/down the answer to nearest hole £ ie 999 from 999.06?

#### Makrini

##### Well-known Member
=round(theformula,0)

#### jasonb75

##### Well-known Member
Thanks
That sort of works the issue i am having is now...
£615*25%+100*15%=999.06
Excel says £168.75
not sure why any ideas?
Thanks
Jamie

That result is correct, for the result to show 999.06 you would need

=ROUND((615*125%+100)*115%,2)

or =ROUND((615+(615*25%)+100)+((615+(615*25%)+100)*15%))

Your existing formula is taking 25% of 615 (153.75) + 15% of 100 (15) = 168.75

Note the use of the ROUND function to give a true £0.00 figure, this prevents any incorrect fraudulent calculations as a result of adding up fractions of a penny.

For example, your figures above result to

£999.0625

without round
£999.0625 *2 = £1998.125

Excel working to 2 decimal places would show this as
£999.06
+£999.06
=£1998.13

With ROUND it would show = £1998.12 Which is the correct figure.

Hope this helps and makes sense

JB

edit: looks like this was resolved while I was writing my reply, but I'll leave it in for info anyway.

Replies
2
Views
104
Replies
13
Views
354
Replies
3
Views
217
Replies
6
Views
966
Replies
1
Views
99

1,191,709
Messages
5,988,237
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back