Adding with percantages and more

[Jamie mills]

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
Thanks in advance
Jamie

 

[Makrini]

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]

I am trying to....
take the value in the cell say £500 add 25% then add 100 the add 15% answer is £833.75

Hope this makes sense
Jamie

 

[XLXRider]

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

 

[Makrini]

actually a Macro for something like this is quite simple.

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]

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]

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]

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]

=round(theformula,0)

 

[jasonb75]

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.