S
Sande Leonardo
how do I tell a formula to divide two cells, leave a "0" if that, and leave
the number is there is a number?
the number is there is a number?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
bj
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
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
S
Sande Leonardo
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.
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.
B
bj
=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.
this checks if both are numbers and that the demonimator is not zero befor
dividing and if not will give a zero.
S
Sande Leonardo
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
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
D
David Biddulph
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.
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.
S
Sande Leonardo
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
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 said: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 said: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
B
bj
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
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
D
David Biddulph
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
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 said: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 said: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
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:
=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.:
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.
:
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
:
how do I tell a formula to divide two cells, leave a "0" if
that,
and leave
the number is there is a number?
S
Sande Leonardo
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.
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.
B
bj
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.
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.
B
bj
Mia culpa, most of the time I check the formula in Excel.
I got lazy this time and didn't.
thanks for finding it.
I got lazy this time and didn't.
thanks for finding it.
David Biddulph said: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 said: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 75David Biddulph said: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
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
:
=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.
:
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.
:
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
:
how do I tell a formula to divide two cells, leave a "0" if
that,
and leave
the number is there is a number?
S
Sande Leonardo
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!!!!
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!!!!