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

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

"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

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

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

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

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