Returning Zero

[Guest]

I use the MAX command for negative numbers when a positive number is
subtracted from zero or empty cells, returning a negative number, but that
does not work when subtracting zero or empty cells from a positive cell. Is
there a formula that will return a zero if either of the values in any of the
cells being subtracted is empty or a formula that would cover both situations?
Thanks

 

[Bob Phillips]

=IF(OR(A12=0,B12=0),0,MAX(A12-B12,0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Bernard Liengme]

Not sure if I get the question but try =IF(OR(A1=0,B1=0),0,A1-B1)
best wishes

 

[Guest]

I think I understand... You are using something like this formula

=MAX(A1-B1,0)

to return 0 or a positive number. But you want something that returns zero
if either cell is empty (or equal to 0). Try this...

=(A1-B1)*(A1<>0)*(B1<>0)

 

[Guest]

This works for empty or zero cells, in Excel, that would return a positive
when subtracted from a positive cell, but not when positive cells are
subtracted from zero or empty. Can this be tweaked a bit?
Thanks for your help

 

[Guest]

This works for empty or zero cells, in Excel, that would return a positive
when subtracted from a positive cell, but not when positive cells are
subtracted from zero or empty. Can this be tweaked a bit?
Thanks for your help

 

[Bob Phillips]

I quote your original post ...

Is there a formula that will return a zero if either of the values in any of
the
cells being subtracted is empty

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Your right, that's what I'm trying to do but the formula, unfortunately,
still doesn't work. Maybe I should explain in more detail. I was trying to
keep it simple but it doesn't seem like it's going to be that simple.
I'm working with dates.
Just for visualizations sake, I'm subtracting two cells to get the number
of days spent on a drawing on my project. I subtract the "start date" from
the "end date" and I get my answer. Simple. If the project isn't finished
but the drawing is then, of course, the "end date" for the project is empty.
If the "end date" is empty then "end date" minus "start date", (0-n), is less
than zero, that is handled with the MAX command, (thank you all very much).
But, if I enter "end date" with no "start date" (n-0), (which can happen in a
slightly different scenerio for me) then of course I will get a positive
return which is untrue.
The formulas I have been getting are helpfull but only work in one
situation or the other but not both. I have tried tweaking the formulas
being suggested but I haven't been able to make anything work. I hoper there
is something that will work.
Thanks for listening (reading).

 

[captlb]

thanks this is what i was looking for, very helpful

 

[Shane Devenshire]

Hi,

If the following formula is returning what you want:

=(A1-B1)*(A1<>0)*(B1<>0)

then you might try this formula:

=(A1-B1)*(A1*B1)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Shane Devenshire]

Hi,

I need to correct the formula I just sent to read

=(A1-B1)*(A1*B1<>0)

cheers,
Shane Devenshire