problems with if function
 

I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong

problems with if function
 

Hi

It sounds as though you have a space character in A1 or B1 or Both.
Do you have a formula in them which is set to return a Null if false?
If so, this formula may be incorrect. For example in A1
=IF(D5=50,5,"") may have been wrongly entered as =IF(D5=50,5," ")

You could amend your formula to
=IF(COUNT(A1:B1)<2,"",B1/A1)
which will work whether there are spaces or nulls in A1 and B1


--
Regards

Roger Govier


"novice" wrote in message
...
I would like c1 to display b1/a1 if a1 and b1 not empty but would like
c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong

problems with if function
 

"I would like c1 to display b1/a1"

=IF(AND(A1="",B1=""),"", B1/A1)

when b1=3, a1=5

1. do you want to literally display as "3/5" or
2. do you want to display the result value as 0.6. or

when b1 is blank, a1=5 (vice versa)
3. do you want to literally display as "/5" or
2. do you want to display the result as blank"".

this counter question is for philosophical understanding of your need.

"novice" wrote:

I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong

problems with if function
 

I would like option 2 for a result in both cases

"driller" wrote:

"I would like c1 to display b1/a1"

=IF(AND(A1="",B1=""),"", B1/A1)

when b1=3, a1=5

1. do you want to literally display as "3/5" or
2. do you want to display the result value as 0.6. or

when b1 is blank, a1=5 (vice versa)
3. do you want to literally display as "/5" or
2. do you want to display the result as blank"".

this counter question is for philosophical understanding of your need.

"novice" wrote:

I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong

problems with if function
 

Hi, this worked as long as I put in a value larger than 0. In this case I
changed your example to IF(COUNT(A1:B1)<0.01,"",B1/A1) so I could use it for
basically all values. However, if I write IF(COUNT(A1:B1)<0,"",B1/A1) I
still get #VALUE!

"Roger Govier" wrote:

Hi

It sounds as though you have a space character in A1 or B1 or Both.
Do you have a formula in them which is set to return a Null if false?
If so, this formula may be incorrect. For example in A1
=IF(D5=50,5,"") may have been wrongly entered as =IF(D5=50,5," ")

You could amend your formula to
=IF(COUNT(A1:B1)<2,"",B1/A1)
which will work whether there are spaces or nulls in A1 and B1


--
Regards

Roger Govier


"novice" wrote in message
...
I would like c1 to display b1/a1 if a1 and b1 not empty but would like
c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong

problems with if function
 

hi novice,

it seems your question is already served by a checked...

you can post another ?thread for another topic..

happy holidays...hohoho

"novice" wrote:

I would like option 2 for a result in both cases

"driller" wrote:

"I would like c1 to display b1/a1"

=IF(AND(A1="",B1=""),"", B1/A1)

when b1=3, a1=5

1. do you want to literally display as "3/5" or
2. do you want to display the result value as 0.6. or

when b1 is blank, a1=5 (vice versa)
3. do you want to literally display as "/5" or
2. do you want to display the result as blank"".

this counter question is for philosophical understanding of your need.

"novice" wrote:

I would like c1 to display b1/a1 if a1 and b1 not empty but would like c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong

problems with if function
 

Hi

That will never work.
The count is being used to ensure that there are numeric values in A1
and B1.
If there are, Count will be 2 and the calculation B1/A1 will work.
If the count is less than 2, then either or both cells do not contain
numeric values, and the cell will show a null.
Use the formula exactly as I posted.
=IF(COUNT(A1:B1)<2,"",B1/A1)

--
Regards

Roger Govier


"novice" wrote in message
...
Hi, this worked as long as I put in a value larger than 0. In this
case I
changed your example to IF(COUNT(A1:B1)<0.01,"",B1/A1) so I could use
it for
basically all values. However, if I write IF(COUNT(A1:B1)<0,"",B1/A1)
I
still get #VALUE!

"Roger Govier" wrote:

Hi

It sounds as though you have a space character in A1 or B1 or Both.
Do you have a formula in them which is set to return a Null if false?
If so, this formula may be incorrect. For example in A1
=IF(D5=50,5,"") may have been wrongly entered as =IF(D5=50,5," ")

You could amend your formula to
=IF(COUNT(A1:B1)<2,"",B1/A1)
which will work whether there are spaces or nulls in A1 and B1


--
Regards

Roger Govier


"novice" wrote in message
...
I would like c1 to display b1/a1 if a1 and b1 not empty but would
like
c1 to
remain empty if nothing in a1 and b1

the following works but only if there is a value in a1 and b1.
=IF(AND(A1="", B1=""),"", B1/A1)

if a1 and b1 empty c1 shows hashkey value!

what am I doing wrong