how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a "0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero befor
dividing and if not will give a zero.
"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a "0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?

Sorry that did not work.

What I have is =sum(A1/B1) and the answer I get is a number or if the
number is zero I get in cell A# is #DIV/0!

I am trying to not get the #DIV/0! I want a plain 0 in A3

So I need the A1/B1 with something to make it say 0 is the answer is 0.

thanks for all you help

"bj" wrote:

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero befor
dividing and if not will give a zero.
"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a "0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?

bj gave you a formula most recently for dealing with A2/A1.
If you are getting problems with A1/B1 did you replace bj's A1 references
with B1, and did you replace his A2 references with A1?
[Or otherwise, use bj's original formula which was written for A1/B1.]
Why not copy the formula you are using and paste it here?
You couldn't have got a #DIV/0 if you had used either of bj's formulae
appropriately.
--
David Biddulph

"Sande Leonardo" wrote in message
...
Sorry that did not work.

What I have is =sum(A1/B1) and the answer I get is a number or if the
number is zero I get in cell A# is #DIV/0!

I am trying to not get the #DIV/0! I want a plain 0 in A3

So I need the A1/B1 with something to make it say 0 is the answer is 0.

thanks for all you help

"bj" wrote:

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero
befor
dividing and if not will give a zero.

"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a
"0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that,
and leave
the number is there is a number?

I copied BJ's formula and changed the A's and B's where I thought it would be
appropriate. Nothing I did to his formula gave me a normal answer.

The #DIV/0! is from my simple formula.

I thought it would be helpful if I showed what I was getting.

Below is BJ's formula and the first column is A1, the second column is B1
the third column is C1 - where the formula is.
I need C1 to show either the divided answer or a 0

75 1 =if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
75 1 75




"David Biddulph" wrote:

bj gave you a formula most recently for dealing with A2/A1.
If you are getting problems with A1/B1 did you replace bj's A1 references
with B1, and did you replace his A2 references with A1?
[Or otherwise, use bj's original formula which was written for A1/B1.]
Why not copy the formula you are using and paste it here?
You couldn't have got a #DIV/0 if you had used either of bj's formulae
appropriately.
--
David Biddulph

"Sande Leonardo" wrote in message
...
Sorry that did not work.

What I have is =sum(A1/B1) and the answer I get is a number or if the
number is zero I get in cell A# is #DIV/0!

I am trying to not get the #DIV/0! I want a plain 0 in A3

So I need the A1/B1 with something to make it say 0 is the answer is 0.

thanks for all you help

"bj" wrote:

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero
befor
dividing and if not will give a zero.

"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a
"0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that,
and leave
the number is there is a number?

I do not understand why you have the sum() portion of the equation.
Is this part of a bigger equation? If so please put the larger equation in
your response.
sum(A1/B1) or just A1/B1 will give a /0 error if B1 is 0
the simplest solution is to put
if(B1=0,0,A1/B1)
in place of A1/B1

"Sande Leonardo" wrote:

Sorry that did not work.

What I have is =sum(A1/B1) and the answer I get is a number or if the
number is zero I get in cell A# is #DIV/0!

I am trying to not get the #DIV/0! I want a plain 0 in A3

So I need the A1/B1 with something to make it say 0 is the answer is 0.

thanks for all you help

"bj" wrote:

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero befor
dividing and if not will give a zero.
"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a "0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?

You'll have spotted (or Excel will have spotted for you) that bj's formula
had a missing parenthesis, so correct that to
=IF(AND(ISNUMBER(A1),ISNUMBER(A2)),IF(A1<0,A2/A1,0),0)

Now change A1 to B1, change A2 to A1.
Try it.
If you still get a divide by zero, copy the exact formula you are using and
paste it here.
--
David Biddulph

"Sande Leonardo" wrote in message
...
I copied BJ's formula and changed the A's and B's where I thought it would
be
appropriate. Nothing I did to his formula gave me a normal answer.

The #DIV/0! is from my simple formula.

I thought it would be helpful if I showed what I was getting.

Below is BJ's formula and the first column is A1, the second column is B1
the third column is C1 - where the formula is.
I need C1 to show either the divided answer or a 0

75 1 =if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
75 1 75

"David Biddulph" wrote:

bj gave you a formula most recently for dealing with A2/A1.
If you are getting problems with A1/B1 did you replace bj's A1 references
with B1, and did you replace his A2 references with A1?
[Or otherwise, use bj's original formula which was written for A1/B1.]
Why not copy the formula you are using and paste it here?
You couldn't have got a #DIV/0 if you had used either of bj's formulae
appropriately.
--
David Biddulph

"Sande Leonardo" wrote in
message
...
Sorry that did not work.

What I have is =sum(A1/B1) and the answer I get is a number or if the
number is zero I get in cell A# is #DIV/0!

I am trying to not get the #DIV/0! I want a plain 0 in A3

So I need the A1/B1 with something to make it say 0 is the answer is 0.

thanks for all you help

"bj" wrote:

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero
befor
dividing and if not will give a zero.

"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a
"0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if
that,
and leave
the number is there is a number?

I put your formula if(B1=0,0,A1/B1) and it just sat there and did not give a
number.

Is there a way I can email you the excel file, so you can see this. It
seems so simple but we don't seem to be understanding each other. I know
it's proably me... but this is now a ventetta to make it work.

There is no larger equation. It is a simple division formula that produces
that #DIV/0 if the answer is 0 this is my whole formula =sum(A1/B1)

It works, it divides the two cells and produces a number.
Or it produces the #DIV/0 if the answer is zero.

I tried pasting the excel into this but that didn't work.

did you have = in front of the equation?
if you copied the equation and pasted it , it may have formated itself as
text,
check the format cell that it is general
select the cell and highlite the "=" replace it with another =

Seems strange, but sometimes works.


"Sande Leonardo" wrote:

I put your formula if(B1=0,0,A1/B1) and it just sat there and did not give a
number.

Is there a way I can email you the excel file, so you can see this. It
seems so simple but we don't seem to be understanding each other. I know
it's proably me... but this is now a ventetta to make it work.

There is no larger equation. It is a simple division formula that produces
that #DIV/0 if the answer is 0 this is my whole formula =sum(A1/B1)

It works, it divides the two cells and produces a number.
Or it produces the #DIV/0 if the answer is zero.

I tried pasting the excel into this but that didn't work.

Mia culpa, most of the time I check the formula in Excel.
I got lazy this time and didn't.
thanks for finding it.

"David Biddulph" wrote:

You'll have spotted (or Excel will have spotted for you) that bj's formula
had a missing parenthesis, so correct that to
=IF(AND(ISNUMBER(A1),ISNUMBER(A2)),IF(A1<0,A2/A1,0),0)

Now change A1 to B1, change A2 to A1.
Try it.
If you still get a divide by zero, copy the exact formula you are using and
paste it here.
--
David Biddulph

"Sande Leonardo" wrote in message
...
I copied BJ's formula and changed the A's and B's where I thought it would
be
appropriate. Nothing I did to his formula gave me a normal answer.

The #DIV/0! is from my simple formula.

I thought it would be helpful if I showed what I was getting.

Below is BJ's formula and the first column is A1, the second column is B1
the third column is C1 - where the formula is.
I need C1 to show either the divided answer or a 0

75 1 =if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
75 1 75

"David Biddulph" wrote:

bj gave you a formula most recently for dealing with A2/A1.
If you are getting problems with A1/B1 did you replace bj's A1 references
with B1, and did you replace his A2 references with A1?
[Or otherwise, use bj's original formula which was written for A1/B1.]
Why not copy the formula you are using and paste it here?
You couldn't have got a #DIV/0 if you had used either of bj's formulae
appropriately.
--
David Biddulph

"Sande Leonardo" wrote in
message
...
Sorry that did not work.

What I have is =sum(A1/B1) and the answer I get is a number or if the
number is zero I get in cell A# is #DIV/0!

I am trying to not get the #DIV/0! I want a plain 0 in A3

So I need the A1/B1 with something to make it say 0 is the answer is 0.

thanks for all you help

"bj" wrote:

=if(and(isnumber(A1),isnumber(A2),if(A1<0,A2/A1,0),0)
this checks if both are numbers and that the demonimator is not zero
befor
dividing and if not will give a zero.

"Sande Leonardo" wrote:

I have a number in cell A1, and in A2.
I need to divide A2 by A1 into A3 (that I can do)

I need A3 to be the answer in the form of a number or have it show a
"0"

something like - If A3<1, "0", otherwise put the answer there.

"bj" wrote:

I am not sure what you want but maybe something like
numerator in Cell A1
Denominator in Cell B1
=if(or(B1=0,b1=""),0,A1/B1)

the simple response to your question might also be
=A1/B1

"Sande Leonardo" wrote:

how do I tell a formula to divide two cells, leave a "0" if
that,
and leave
the number is there is a number?

just used a pop up error for research-- this is what fixed my problem
It gave me what ever I typed in, I chose the word "zero" - just for fun

here is the formula that worked - =IF(B2=0,"zero",A2/B2)

Told you this was a vendetta ... and I want to thank you for trying so hard
to help me. It was only through your help and some tweeking that I
accomplished my task.

Again thank you!!!!