calculating a sum that contains a 0 or no data

[Martin]

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

 

[Mike H]

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

 

[sanjay D.]

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.
(e-mail address removed)

 

[Martin]

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

 

[Martin]

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

 

[David Biddulph]

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

 

[Mike H]

Glad I could help and thanks for the feedback

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]

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