| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Returning Zero
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 -- Silvercreek |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
=IF(OR(A12=0,B12=0),0,MAX(A12-B12,0))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Silvercreek" wrote in message ... 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 -- Silvercreek |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
Not sure if I get the question but try =IF(OR(A1=0,B1=0),0,A1-B1)
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Silvercreek" wrote in message ... 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 -- Silvercreek |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
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) "Silvercreek" wrote: 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 -- Silvercreek |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
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 -- Silvercreek "Bob Phillips" wrote: =IF(OR(A12=0,B12=0),0,MAX(A12-B12,0)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Silvercreek" wrote in message ... 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 -- Silvercreek |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
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 -- Silvercreek "Bernard Liengme" wrote: Not sure if I get the question but try =IF(OR(A1=0,B1=0),0,A1-B1) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Silvercreek" wrote in message ... 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 -- Silvercreek |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
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) "Silvercreek" wrote in message ... 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 -- Silvercreek "Bob Phillips" wrote: =IF(OR(A12=0,B12=0),0,MAX(A12-B12,0)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Silvercreek" wrote in message ... 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 -- Silvercreek |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
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). -- Silvercreek "Sloth" wrote: 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) "Silvercreek" wrote: 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 -- Silvercreek |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
thanks this is what i was looking for, very helpful
"Sloth" wrote: 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) "Silvercreek" wrote: 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 -- Silvercreek |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
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 "captlb" wrote in message ... thanks this is what i was looking for, very helpful "Sloth" wrote: 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) "Silvercreek" wrote: 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 -- Silvercreek |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Returning Zero
Hi,
I need to correct the formula I just sent to read =(A1-B1)*(A1*B1<0) cheers, Shane Devenshire "captlb" wrote in message ... thanks this is what i was looking for, very helpful "Sloth" wrote: 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) "Silvercreek" wrote: 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 -- Silvercreek |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP returning value when range_lookup false | Excel Worksheet Functions | |||
| My IF in Excel formula is not returning the correct value. | Excel Worksheet Functions | |||
| vlookup not returning full field value (text) | Excel Worksheet Functions | |||
| LOOKUP returning value in cell above what I was searching for | Excel Discussion (Misc queries) | |||
| MY VLOOKUP IS RETURNING N/A AND THEREFORE MY TOTAL SUM AT THE BOT. | Excel Discussion (Misc queries) | |||
Linear Mode
