circular error problem for vat calc
 

Hi, I want to be able to put a figure in a cell that is inclusive of VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?

Thanks in advance

circular error problem for vat calc
 

Of course you're going to have a cicular error if you put the formula in the
same box as the data entry. So don't do that. Enter 117.50 in cell A6. Put
your formula in *any other* cell, like A7.

Regards,
Fred.

"Steve" wrote in message
...
Hi, I want to be able to put a figure in a cell that is inclusive of VAT
and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?

Thanks in advance

circular error problem for vat calc
 

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?

Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

You are correct with your understanding, but in the table I am trying to
create, I want it in the same cell and not in another as you suggest. I have
a series of columns with different headings that I want the amount in less
the VAT. I have to put that in first and then it will be calculated in a
totals column inclusive of VAT. As I would have the figures inc. VAT it would
save me the time calculating the - VAT before I put it. If I use a different
cell I would have twice as many and it wouldn't make any sense. I hope this
explains it, I know it it is not a common thing to do, thats why I need help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?

Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

How much extra do you have to pay to Microsoft to use the extra cells? :-)
But if you prefer to devise a VBA solution rather than using the simple
formula option, and if you are happy that you can keep track of which of the
values in the same column ae including VAT and which are excluding, then you
are welcome to do so.
--
David Biddulph

"Steve" wrote in message
...
You are correct with your understanding, but in the table I am trying to
create, I want it in the same cell and not in another as you suggest. I
have
a series of columns with different headings that I want the amount in less
the VAT. I have to put that in first and then it will be calculated in a
totals column inclusive of VAT. As I would have the figures inc. VAT it
would
save me the time calculating the - VAT before I put it. If I use a
different
cell I would have twice as many and it wouldn't make any sense. I hope
this
explains it, I know it it is not a common thing to do, thats why I need
help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of
VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box
A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it
gives a
circular reference error. I tried doing what the help said and ticking
the
iteration box but it didnt seem to help. Is there any way of doing
this?

Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

Perhaps you chould create insert a new worksheet that mirrors the values
(less VAT) using =Sheet1!A1/1.175 (assuming the initial value is in worksheet
'Sheet1' and cell A1.
Dragging this formula should change the cell references as it is pasted
avoiding the need to type into every cell.

"Steve" wrote:

You are correct with your understanding, but in the table I am trying to
create, I want it in the same cell and not in another as you suggest. I have
a series of columns with different headings that I want the amount in less
the VAT. I have to put that in first and then it will be calculated in a
totals column inclusive of VAT. As I would have the figures inc. VAT it would
save me the time calculating the - VAT before I put it. If I use a different
cell I would have twice as many and it wouldn't make any sense. I hope this
explains it, I know it it is not a common thing to do, thats why I need help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?

Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

Don't really understand what you are getting at. I don't want to put the
value in a different cell because I want to keep the table simple, if you
could see what I was trying to achieve it would make more sense. I don't know
how to devise a VBA solution, that's what I need help with....

"David Biddulph" wrote:

How much extra do you have to pay to Microsoft to use the extra cells? :-)
But if you prefer to devise a VBA solution rather than using the simple
formula option, and if you are happy that you can keep track of which of the
values in the same column ae including VAT and which are excluding, then you
are welcome to do so.
--
David Biddulph

"Steve" wrote in message
...
You are correct with your understanding, but in the table I am trying to
create, I want it in the same cell and not in another as you suggest. I
have
a series of columns with different headings that I want the amount in less
the VAT. I have to put that in first and then it will be calculated in a
totals column inclusive of VAT. As I would have the figures inc. VAT it
would
save me the time calculating the - VAT before I put it. If I use a
different
cell I would have twice as many and it wouldn't make any sense. I hope
this
explains it, I know it it is not a common thing to do, thats why I need
help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of
VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box
A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it
gives a
circular reference error. I tried doing what the help said and ticking
the
iteration box but it didnt seem to help. Is there any way of doing
this?

Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

Ok but you said in another column you will be calculating inclusive of
VAT, so you are already using two cells and doing a calculation on each
item. What's the difference? Other than you want to add an additional,
unnecessary calculation?

I'm not trying to be stubborn here, I just don't understand the purpose
of backing into a number you've already backed out of. That is, apropos
to your title, quite circular indeed.

Plus, there is some inherent risk in modifying user input on the fly
when best practice would suggest letting that input stand on its own
merits and making modifications elsewhere to achieve the objective. But
that's just me.

Steve wrote:
You are correct with your understanding, but in the table I am trying to
create, I want it in the same cell and not in another as you suggest. I have
a series of columns with different headings that I want the amount in less
the VAT. I have to put that in first and then it will be calculated in a
totals column inclusive of VAT. As I would have the figures inc. VAT it would
save me the time calculating the - VAT before I put it. If I use a different
cell I would have twice as many and it wouldn't make any sense. I hope this
explains it, I know it it is not a common thing to do, thats why I need help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?
Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

Let me try to explain....

I get a receipt which has the gross amount on it which I will need in a
'totals' column, I also want the net value in one of about eight columns that
describe what the receipt was for ie. fuel, postage, materials etc. If I put
the gross amount in the totals column, I cant see a way of having a formula
to calculate it net of vat in just one of the other columns. Therefore, I
want to put the gross amount in the net column and for it to be changed to
net, the totals column would then be able to add the vat back on.
I am basically trying to save myself the time calculating the amount less
vat before I put it into the table, not the end of the world if I cant but
not unnecessary.

I hope this makes sense, I dont want to start adding columns that don't make
any sense to whoever I present it to. I do appreciate your help, I only know
the real basics of excel but thought there may be a simple solution to the
problem.

"smartin" wrote:

Ok but you said in another column you will be calculating inclusive of
VAT, so you are already using two cells and doing a calculation on each
item. What's the difference? Other than you want to add an additional,
unnecessary calculation?

I'm not trying to be stubborn here, I just don't understand the purpose
of backing into a number you've already backed out of. That is, apropos
to your title, quite circular indeed.

Plus, there is some inherent risk in modifying user input on the fly
when best practice would suggest letting that input stand on its own
merits and making modifications elsewhere to achieve the objective. But
that's just me.

Steve wrote:
You are correct with your understanding, but in the table I am trying to
create, I want it in the same cell and not in another as you suggest. I have
a series of columns with different headings that I want the amount in less
the VAT. I have to put that in first and then it will be calculated in a
totals column inclusive of VAT. As I would have the figures inc. VAT it would
save me the time calculating the - VAT before I put it. If I use a different
cell I would have twice as many and it wouldn't make any sense. I hope this
explains it, I know it it is not a common thing to do, thats why I need help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it gives a
circular reference error. I tried doing what the help said and ticking the
iteration box but it didnt seem to help. Is there any way of doing this?
Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT in
a new cell as =A6/1.175 ?

circular error problem for vat calc
 

Although it's possible to do with VBA, if you're a basic Excel user, my
advice is to avoid it. It won't be easy to proram or maintain.

If you don't want to use an extra cell, why not enter a formula when you
have a VAT-in price. It's just as acceptable in Excel to enter "=117/1.175"
(without the quotes) as "100". This will be the simplest, and gives you an
audit trail in case you make a data entry error.

Regards,
Fred.



"Steve" wrote in message
...
Let me try to explain....

I get a receipt which has the gross amount on it which I will need in a
'totals' column, I also want the net value in one of about eight columns
that
describe what the receipt was for ie. fuel, postage, materials etc. If I
put
the gross amount in the totals column, I cant see a way of having a
formula
to calculate it net of vat in just one of the other columns. Therefore, I
want to put the gross amount in the net column and for it to be changed to
net, the totals column would then be able to add the vat back on.
I am basically trying to save myself the time calculating the amount less
vat before I put it into the table, not the end of the world if I cant but
not unnecessary.

I hope this makes sense, I dont want to start adding columns that don't
make
any sense to whoever I present it to. I do appreciate your help, I only
know
the real basics of excel but thought there may be a simple solution to the
problem.

"smartin" wrote:

Ok but you said in another column you will be calculating inclusive of
VAT, so you are already using two cells and doing a calculation on each
item. What's the difference? Other than you want to add an additional,
unnecessary calculation?

I'm not trying to be stubborn here, I just don't understand the purpose
of backing into a number you've already backed out of. That is, apropos
to your title, quite circular indeed.

Plus, there is some inherent risk in modifying user input on the fly
when best practice would suggest letting that input stand on its own
merits and making modifications elsewhere to achieve the objective. But
that's just me.

Steve wrote:
You are correct with your understanding, but in the table I am trying
to
create, I want it in the same cell and not in another as you suggest. I
have
a series of columns with different headings that I want the amount in
less
the VAT. I have to put that in first and then it will be calculated in
a
totals column inclusive of VAT. As I would have the figures inc. VAT it
would
save me the time calculating the - VAT before I put it. If I use a
different
cell I would have twice as many and it wouldn't make any sense. I hope
this
explains it, I know it it is not a common thing to do, thats why I need
help
:)

"smartin" wrote:

Steve wrote:
Hi, I want to be able to put a figure in a cell that is inclusive of
VAT and
for it to then become the amount less VAT at 17.5 %. ie if I put in
box A6
117.5 I want it to become 100. I know the sum is (A6)/1.175 but it
gives a
circular reference error. I tried doing what the help said and
ticking the
iteration box but it didnt seem to help. Is there any way of doing
this?
Hi Steve,

Let me make sure I understand the dilemma: You want to type 117.5 in
A6
and this cell will then somehow morph to a value of 100?

Perhaps this is possible with VBA code, but why? Why not enter a value
inclusive of VAT in A6 and then calculate the value exclusive of VAT
in
a new cell as =A6/1.175 ?