Excel question

Lokolo

Steve_xx wrote: »

OK I see what you mean. In my equation where the answer is £7, I understand what's happening upto this bit =B15*C15* but I don't quite grasp how this bit works (1-D15) and how you now what to write?

If you are wanting to take 30%. 1 - 30% = 70%, which is what you want to display, 70% of the product price (as 70% is 100% - 30%!)

Steve_xx

Lokolo wrote: »

If you are wanting to take 30%. 1 - 30% = 70%, which is what you want to display, 70% of the product price (as 70% is 100% - 30%!)

Thanks for the clarification on that, I understand it now.

In my post #8 I omitted to add the possibility of VAT being added to the total displayed in D1. So I've made a slight amendment to the question. How would I write this:

A1 contains units of items, say 10
B1 contains the cost of each unit, say £1.00
C1 contains the discount percentage, say 30%
D1 contains the answer, ie A1 multiplied by B1, then discounted by the amount in C1 and then VAT is added @ 17.5%

Lokolo

Steve_xx wrote: »

Thanks for the clarification on that, I understand it now.

In my post #8 I omitted to add the possibility of VAT being added to the total displayed in D1. So I've made a slight amendment to the question. How would I write this:

A1 contains units of items, say 10
B1 contains the cost of each unit, say £1.00
C1 contains the discount percentage, say 30%
D1 contains the answer, ie A1 multiplied by B1, then discounted by the amount in C1 and then VAT is added @ 17.5%

To add VAT on

If 1 = 100%. What does 117.5% = ? 1.175.

* the answer by 1.175 to add VAT.

Steve_xx

Lokolo wrote: »

To add VAT on

If 1 = 100%. What does 117.5% = ? 1.175.

* the answer by 1.175 to add VAT.

ok, so to get the answer in D1 I write as follows:

=B21*C21*(1-D21)*1.175

It seems to work!

Stompa

Steve_xx wrote: »

ok, so to get the answer in D1 I write as follows:

=B21*C21*(1-D21)*1.175

It seems to work!

Yes, that's fine, though you may want to put the 17.5% in it's own cell so that it's easily amended when they put VAT up!

Steve_xx

Stompa wrote: »

Yes, that's fine, though you may want to put the 17.5% in it's own cell so that it's easily amended when they put VAT up!

I might put VAT in a cell on the same row like this

A1 contains units of items, say 10
B1 contains the cost of each unit, say £1.00
C1 contains the discount percentage, say 30%
D1 contains the VAT @17.5% and formatted to percentages
E1 contains the answer, ie A1 multiplied by B1, then discounted by the amount in C1 and then VAT is added @ 17.5%

My logic tell me to write this in E1, =A1*B1*(1-C1)+D1 and that gives me an answer of £7.18 which is incorrect of course!

Stompa

Steve_xx wrote: »

I might put VAT in a cell on the same row like this

A1 contains units of items, say 10
B1 contains the cost of each unit, say £1.00
C1 contains the discount percentage, say 30%
D1 contains the VAT @17.5% and formatted to percentages
E1 contains the answer, ie A1 multiplied by B1, then discounted by the amount in C1 and then VAT is added @ 17.5%

My logic tell me to write this in E1, =A1*B1*(1-C1)+D1 and that gives me an answer of £7.18 which is incorrect of course!

You actually want:

=A1*B1*(1-C1)*(1+D1)

having said that, if VAT is going to be the same percentage throughout you should probably just have it in a single cell rather than on each row.....

Steve_xx

Thanks for helping me. I'm sorted now. Will I ever get to grips fully with Excel?