I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Martin,

Your formula for calculting the VAT from the gross is incorrect. Consider
this:-

A gross of £117.5 is clearly £100 with 17.5% VAT added but using your
formula gives a vat amount of 20.56 so use this instead:-

=IF(X13=0,"",(X13/117.5*17.5))

Now to get rid of the value error use this
=IF(X13<0,X13-X14,"")

Mike





"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Dear martin,
=IF(X13="","",X13*17.5%) should be the formula for Vat cell and
=IF(X14="","",SUM(X13-X14)) should be the formula for gross cell instead of
typing zero, always type "". Hope this will work for you.


"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Hi Mike,

Yes this worked to an extent however using these formats the vat at 10 comes
out at 1.49, it should be 1.75 so it is not calculating the vat correctly. I
also tried the calculation on 40.03 the vat comes out at 5.96 it should be
7.00, hope you can help

"Mike H" wrote:

Martin,

Your formula for calculting the VAT from the gross is incorrect. Consider
this:-

A gross of £117.5 is clearly £100 with 17.5% VAT added but using your
formula gives a vat amount of 20.56 so use this instead:-

=IF(X13=0,"",(X13/117.5*17.5))

Now to get rid of the value error use this
=IF(X13<0,X13-X14,"")

Mike





"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Sorry Mike,

I was backward calculating based on the net figure vat, my mistake.
obviousley the gross figure is the net + vat. got it now , just a bit tiered
thanks again

"Mike H" wrote:

Martin,

Your formula for calculting the VAT from the gross is incorrect. Consider
this:-

A gross of £117.5 is clearly £100 with 17.5% VAT added but using your
formula gives a vat amount of 20.56 so use this instead:-

=IF(X13=0,"",(X13/117.5*17.5))

Now to get rid of the value error use this
=IF(X13<0,X13-X14,"")

Mike





"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Perhaps you would care to tell us why you have used SUM(X13-X14) instead of
just X13-X14, Sanjay ?
--
David Biddulph

"sanjay D." wrote in message
...
Dear martin,
=IF(X13="","",X13*17.5%) should be the formula for Vat cell and
=IF(X14="","",SUM(X13-X14)) should be the formula for gross cell instead
of
typing zero, always type "". Hope this will work for you.


"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter
gross
amount and the vat and net figures are calculated accordingly, I can get
this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return
a
blank cell ok, but how do I get the net cell to return a blank cell if
either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Glad I could help and thanks for the feedback

"Martin" wrote:

Sorry Mike,

I was backward calculating based on the net figure vat, my mistake.
obviousley the gross figure is the net + vat. got it now , just a bit tiered
thanks again

"Mike H" wrote:

Martin,

Your formula for calculting the VAT from the gross is incorrect. Consider
this:-

A gross of £117.5 is clearly £100 with 17.5% VAT added but using your
formula gives a vat amount of 20.56 so use this instead:-

=IF(X13=0,"",(X13/117.5*17.5))

Now to get rid of the value error use this
=IF(X13<0,X13-X14,"")

Mike





"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help

Sanjay,

=IF(X13="","",X13*17.5%) should be the formula for Vat cell and

This won't calculate the VAT from the gross figure. If you add 17.5% to a
number you can't then simply subtract 17.5% to get back to where you started.

Mike

"sanjay D." wrote:

Dear martin,
=IF(X13="","",X13*17.5%) should be the formula for Vat cell and
=IF(X14="","",SUM(X13-X14)) should be the formula for gross cell instead of
typing zero, always type "". Hope this will work for you.


"Martin" wrote:

I am trying to create a worksheet that calculates finances over three
columns, these columns are net, vat and gross, it is designed to enter gross
amount and the vat and net figures are calculated accordingly, I can get this
to run with no problems, however if the gross cell is blank the net cell
shows a value error. I am using the IF command on the vat cell to return a
blank cell ok, but how do I get the net cell to return a blank cell if either
of the other cells are blank?

formular for vat cell is =IF(X13=0,"",X13*17.5%)
X13 is the gross cell

formular for the net cell is =X13-X14
X14 is the vat cell

if there is no data in X13 the net result shows the VALUE error as the
calculation obviously contains a blank or zero.

please help