And, if, arguments....

[Guest]

I am trying to get a cell to do this:

Look at cell B7, if B7 has a vlaue of greater than or equal to 0, return a
value of 4 to cell Z4, if cell B7 is empty return a value of 0 to cell Z4....

Can you help?

 

[Guest]

=IF(B7>=0,4,IF(ISBLANK(B7),0))

Dave

 

[Dave Peterson]

Put this formula in Z4:
=if(b7>=0,4,"whatdoyouwanthere?")

(Excel will treat an empty cell as 0.)

 

[Guest]

I entered
=IF(B10>=0,4,IF(ISBLANK(B10),0))
and it is still giving me a value of 4 when the cell in blank.....

 

[Guest]

I put a 0 between the quotes, and am still getting a value of 4 in the cell
as if excel is seeing a blank cell as a value >= zero....

 

[Guest]

No quote between 0

=IF(B7>=0,4,"")

 

[Guest]

Ignore my first reply
Here is the correction formula
=IF(B7="","",IF(B7>=0,4))

 

[Guest]

Hi Chuck,

Does B7 contains a formula that may give results from an error or "" or
negative values ?
Does B7 shall contain type-in values ?

 

[David Biddulph]

You probably need to put Dave's formula the other way round:
=IF(ISBLANK(B10),0,IF(B10>=0,4,"undefined"))

 

[Dave Peterson]

And I misread your requirements...

=if(b7="",0,if(b7>=0,4,"whatdoyouwanthere?"))

 

[Guest]

Yes, you're right. Sorry about that. Brain's frozen!

Dave

 

[Guest]

Thanks for your help Mama,

But, it doesn't work... still getting 4 for a blank cell....

 

[Guest]

Thanks again... still getting the 4 value though....

 

[Guest]

Dave,

I put a zero between the quotes, to reflect the balnk field... Still getting
a 4.....

 

[Guest]

If I undertsna dyour question correctly, the B7 field will either be blank
(as in this case) or have a value between 0 and 4.... The formula I am
looking for will "look" at that B7 cell and either return with a blank or
zero (in the case of a blank B7 cell) or return with a value of 4 if a value
of 0 - 4 is entered into B7....

Hope this wasn't too confusing...

Could it be some type of formatting issue?

 

[Dave Peterson]

Share your formula.

And what's in B7.

Maybe...

=if(trim(b7)="",0,if(b7>=0,4,0))

(Don't use "0" if you want a real number--not text.)

Dave,

I put a zero between the quotes, to reflect the balnk field... Still getting
a 4.....

 

[Guest]

Hi Mo,

from the original post..this means IF B7="" THEN 0
=if(b7="",0,4) -----this may be the case


then you mentioned a while agothis means IF AND(B7>=0,B7<=4) THEN 4

=IF (B7="","",IF(AND(B7>=0,B7<=4),4,"don't exceed 4"))

Are these something you can work with?

regards

regards

 

[David Biddulph]

In which case, if your cell looks blank but returns the 4, you've probably
got one or more spaces (or other invisible characters) in a text string in
B7. What does =LEN(B7) give you? If it doesn't return 0, go into B7 and
delete the spaces or whatever is there.

 

[Gord Dibben]

As far as I can tell, Excel treats a blank cell as zero(0) so >=0 will
return 4.

Try it with >0 and see what occurs.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks!
Thats it..... The formula wasn't working unless I clicked in the blank cell
and hit the delete key, so, although it looked empty, it had something in it!