Gretaer Than Zero Formula Question


M

mlv

I have a formula that puts a currency value in a cell (assume A1).
That value can be anything from £0.00 upwards.

Dependent on other factors, if the calculated value of Cell A1 is £0.00
(zero pounds), the formula may enter £0.00, or it may leave the cell blank
(i.e. using value "" in the formula).

An adjacent cell (assume B1) has to perform a calculation on Cell A1 only if
the value of Cell A1 is greater than £0.00 (i.e. £0.01 and upwards).

I was trying the formula:

=IF(A1>0,ROUND((D6/(100-N6)*N6),2),"")

The formula works fine when Cell A1 is £0.00 (Cell B1 shows blank) or when
Cell A1 shows a value above £0.00 (Cell B1 shows the calculated value).
However, the formula fails when A1 is blank and I get the #VALUE! error.

Doesn't the formula =IF(A1>0, ... return 'False' when Cell A1 is blank?

How else can I easily check Cell A1 for 'not blank' AND 'greater then zero'?

Thanks
 
Ad

Advertisements

B

Bob Phillips

=IF(AND(A1<=0,A1=""),"",ROUND((D6/(100-N6)*N6),2))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
S

Sandy Mann

Bob, don't you mean:

=IF(OR(A1<=0,A1=""),"",ROUND((D6/(100-N6)*N6),2))

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
M

mlv

Thanks for the suggestions Don & Bob (and the correction Sandy!); I'll give
them a try.

I assume the formula =IF(A1>0, ... ) doesn't like it when Cell A1 is
'blank'?
 
Ad

Advertisements

P

Peo Sjoblom

When cell A1 is "blank" through a formula and a text entry like "" it means
that it is greater than zero, all text entries in Excel are greater than any
numbers so you either need to test for a blank or for a zero character count



--


Regards,


Peo Sjoblom
 
Ad

Advertisements


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


Top