Simple, if you know Excel !!!!

E

Emimec

A simple procedure is proving hard for me I'm afraid !!!

I want to enter a gross amount, next column shows the VAT , next column
shows the net amount. I used the IF formula, but get VALUE if the statement
is false, and I want blank boxes if nothing is entered in amount and vat
columns.

AMOUNT VAT NET

£141.00 £21.00 £120.00
#VALUE!
#VALUE!
#VALUE!
#VALUE!


=IF(A3>1,A3/47*7) (in the VAT column)

=SUM(A3,-B3) (in the NET column)

Advice gratefully received
Bob
 
D

Don Guillett Excel MVP

A simple procedure is proving hard for me I'm afraid !!!

I want to enter a gross amount, next column shows the VAT , next column
shows the net amount. I used the IF formula, but get VALUE if the statement
is false, and I want blank boxes if nothing is entered in amount and vat
columns.

      AMOUNT VAT NET

      £141.00 £21.00 £120.00
      #VALUE!
      #VALUE!
      #VALUE!
      #VALUE!

=IF(A3>1,A3/47*7) (in the VAT column)

=SUM(A3,-B3)  (in the NET column)

Advice gratefully received
Bob
Click to expand...

=IF(LEN(TRIM(A24))>1,A24/47*7,"")
=IF(LEN(TRIM(A24))>1,A24-B24,"")
 
E

Emimec

A simple procedure is proving hard for me I'm afraid !!!

I want to enter a gross amount, next column shows the VAT , next column
shows the net amount. I used the IF formula, but get VALUE if the
statement
is false, and I want blank boxes if nothing is entered in amount and vat
columns.

AMOUNT VAT NET

£141.00 £21.00 £120.00
#VALUE!
#VALUE!
#VALUE!
#VALUE!

=IF(A3>1,A3/47*7) (in the VAT column)

=SUM(A3,-B3) (in the NET column)

Advice gratefully received
Bob
Click to expand...

=IF(LEN(TRIM(A24))>1,A24/47*7,"")
=IF(LEN(TRIM(A24))>1,A24-B24,"")

Many Thanks
I can see I really do need to study Excel, as what I thought was easy, looks
complicated to me.
bob
 
A

alanglloyd

A simple procedure is proving hard for me I'm afraid !!!

I want to enter a gross amount, next column shows the VAT , next column
shows the net amount. I used the IF formula, but get VALUE if the statement
is false, and I want blank boxes if nothing is entered in amount and vat
columns.

      AMOUNT VAT NET

      £141.00 £21.00 £120.00
      #VALUE!
      #VALUE!
      #VALUE!
      #VALUE!

=IF(A3>1,A3/47*7) (in the VAT column)

=SUM(A3,-B3)  (in the NET column)
Click to expand...

Strange, I enter . . .

=A3/47*7
=A3-B3

.. . . and get no errors, just zeroes in columns VAT & NET. Even if I
enter £141, not 141 I get the same, Excel seems to switch to Currency
format & gives £21 & £120.

If you want to get nothing if there's no zero value (ie blank not 0)
then format the cells appropriately. Custom Formats have up to 3
elements separated by semi-colons. the first element for positive
numbers, the next for negative, and the third for zero. So . . .

£#0.00;-£#0.00;""

.. . . gets nothing if the cell has a zero value, and two decimal
places if its positive or negative. One can get really fancy entries
using this method, ie . . .

£#0.00;"not negative";""

But my Excel is 97, perhaps that's why I get different results.

Alan Lloyd
 
E

Emimec

A simple procedure is proving hard for me I'm afraid !!!

I want to enter a gross amount, next column shows the VAT , next column
shows the net amount. I used the IF formula, but get VALUE if the
statement
is false, and I want blank boxes if nothing is entered in amount and vat
columns.

AMOUNT VAT NET

£141.00 £21.00 £120.00
#VALUE!
#VALUE!
#VALUE!
#VALUE!

=IF(A3>1,A3/47*7) (in the VAT column)

=SUM(A3,-B3) (in the NET column)
Click to expand...

Strange, I enter . . .

=A3/47*7
=A3-B3

.. . . and get no errors, just zeroes in columns VAT & NET. Even if I
enter £141, not 141 I get the same, Excel seems to switch to Currency
format & gives £21 & £120.

If you want to get nothing if there's no zero value (ie blank not 0)
then format the cells appropriately. Custom Formats have up to 3
elements separated by semi-colons. the first element for positive
numbers, the next for negative, and the third for zero. So . . .

£#0.00;-£#0.00;""

.. . . gets nothing if the cell has a zero value, and two decimal
places if its positive or negative. One can get really fancy entries
using this method, ie . . .

£#0.00;"not negative";""

But my Excel is 97, perhaps that's why I get different results.

Alan Lloyd

Many thanks, wish I was younger and could learn more of this.
Bob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IF function turning text to number 4
calculating a sum that contains a 0 or no data 7
The If function 4
Vlookup with If condition 1
Macro Relative Cells 3
35% profit set 1
Calculating a Formula Subject to a Maximum and Minimum Value 2
calculating a value if adjacent cell has value 3

Top