Calculation Formula

[bobzy20]

Hi

I’m trying to write a calculation formula that will do the following:

Price £14.99 x 9% + £0.10p minus 10% x 20%

Price £14.99 x 2.9% + £0.20p

Result = £2.20

My attempt is below:

=SUM(D2)*(9%)+(0.1)+(D2)*(9%)*(-10%)+(D2)*(2.9%)+(0.2)

(D2) is the Price £14.99

Any help would be great

Thanks

Bob

 

[Special-K99]

Your calculation is somewhat difficult to make out

You don't need SUM if you only have one parameter in brackets
You don't need brackets round everything, only where that part of the calculation needs to be worked out first.

=(D2*9%+0.1)*90%*20%

Are you sure the answer is £2.20 ?

 

[bobzy20]

Thanks for your reply, yes I made it myself without any real experience.

Yes I made the value £2.20 when working it out on the calculator. This calculation is coming out at £0.26 based on a value of £14.99 (D2) which cant be right as 9% of £14.99 is £1.35.

 

[bobzy20]

I would like all of this below in one formula:

Price £14.99 x 9% + £0.10p minus 10% x 20% - Price £14.99 x 2.9% + £0.20p​

 

[Special-K99]

"Yes I made the value £2.20 when working it out on the calculator."

Calculators don't use BODMAS, they work sequentially. Anything in brackets you would have to do manually first and store the result.

14.99 * 9% = 1.3491
1.3491 + 0.10 = 1.4491
1.4491 - 10% of 1.4491 = 1.30419
1.30419 * 20% = 0.260838

14.99 * 2.9% = 0.43471
0.43471 + 0.20 = 0.63471

0.260838 - 0.63471 = -0.2

How do you get 2.20 ?

 

[bobzy20]

Thanks for your reply, we are getting closer.

Part 1 - 1.30419 + 0.260838 = 1.565028

Part 2 - 1.565028 + 0.63471 = 2.199738

Is there a way of getting all this is one formula to get the result showing £2.20

 

[Special-K99]

"Part 1 - 1.30419 + 0.260838 = 1.565028"

How do you get this figure based on my previous post?

 

[bobzy20]

1.30419 * 20% = 0.260838, then you add them together?

1.30419 + 0.260838 = 1.565028

 

[Special-K99]

Ok try this

So you actually want

(£14.99 x 9% + £0.10) * 90%+((£14.99 x 9% + £0.10) * 90%) * 120%

With 14.99 in A1

=(A1 * 9% + 0.10) * 90% * 120% + A1 * 2.9% + 0.2

 

[bobzy20]

Oh yes, works like a dream, thanks for all your help.

Sorry for any confusion in my sample formula as I'm a complete novice at the mo.