| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excluding Negative Numbers
Thanks mostly to T. Valko and JE McGimpsey I have the following formulas.
1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
may be this
=IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42*pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. * 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. *As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. * Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
It works as long as I have two dates, to subtract, but sometimes I am missing
a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
This assume that if K15 and L15 is not = 2 or that they are negative number,
the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
Perhaps I made a mistake, but it's not returning a blank.
"xlmate" wrote: This assume that if K15 and L15 is not = 2 or that they are negative number, the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
Maybe this
=IF(OR(COUNT(K15:L15)<2,L15<K15),"",NETWORKDAYS(K 15,L15)-1) Mike "Workbook" wrote: Perhaps I made a mistake, but it's not returning a blank. "xlmate" wrote: This assume that if K15 and L15 is not = 2 or that they are negative number, the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
Hi,
apology, this formula will return blank based on your additional requirement over your existing formula =IF(OR(COUNT(K15:L15)<2,L15-K15<0),"",NETWORKDAYS(K15,L15)-1) HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: Perhaps I made a mistake, but it's not returning a blank. "xlmate" wrote: This assume that if K15 and L15 is not = 2 or that they are negative number, the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
Mike, you're the man. It works! I have been trying so hard to get this.
Thank you! "xlmate" wrote: Hi, apology, this formula will return blank based on your additional requirement over your existing formula =IF(OR(COUNT(K15:L15)<2,L15-K15<0),"",NETWORKDAYS(K15,L15)-1) HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: Perhaps I made a mistake, but it's not returning a blank. "xlmate" wrote: This assume that if K15 and L15 is not = 2 or that they are negative number, the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
Thanks for your feedback Mike, I appreciate your help!
"Mike H" wrote: Maybe this =IF(OR(COUNT(K15:L15)<2,L15<K15),"",NETWORKDAYS(K 15,L15)-1) Mike "Workbook" wrote: Perhaps I made a mistake, but it's not returning a blank. "xlmate" wrote: This assume that if K15 and L15 is not = 2 or that they are negative number, the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excluding Negative Numbers
Sorry Francis, I have been up all night. I was writing messages to you and
Mike at the same time. You were right it works. I cannot thank you enough. Ed "xlmate" wrote: Hi, apology, this formula will return blank based on your additional requirement over your existing formula =IF(OR(COUNT(K15:L15)<2,L15-K15<0),"",NETWORKDAYS(K15,L15)-1) HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: Perhaps I made a mistake, but it's not returning a blank. "xlmate" wrote: This assume that if K15 and L15 is not = 2 or that they are negative number, the formula will return a blank. =IF(OR(COUNT(K15:L15)<2,(COUNT(K15:L15)<0)),"",NE TWORKDAYS(K15,L15)-1) Is this what you want? HTH -- Pls provide your feedback by clicking the Yes button below if this post have help you. This will help others to search the archives for result better. Thank You cheers, francis "Workbook" wrote: It works as long as I have two dates, to subtract, but sometimes I am missing a date. Do you know if it's possible to combine this formula =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) with your formula so that if dates are missing, the cell this formula is in will go blank? or if the number that would appear in the cell is negative it will go blank? "muddan madhu" wrote: may be this =IF(SIGN(IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L 15)-1))=-1,"",IF (COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L15)-1)) On Feb 4, 12:42 pm, Workbook wrote: Thanks mostly to T. Valko and JE McGimpsey I have the following formulas. 1) =IF(COUNT(K15:L15)<2,"",NETWORKDAYS(K15,L715)-1) 2) =IF(N15<"",(N15*24),"") Both formulas makes the cell which contains the formula blank when data is missing from one of the two referenced cells. As a result some kind of error message will not appear and the cell stays blank allowing for accurate calculations of other formulas. Do you know how the formulas above could be modified to exclude negative numbers as well, so that the cells which contain these formulas go blank when they calculate a negative number? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excluding Repeating Numbers Help!1 | Excel Worksheet Functions | |||
| Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
| Excluding numbers in a total | Excel Discussion (Misc queries) | |||
| Averaging excluding min and max numbers | Excel Worksheet Functions | |||
| retrieve numbers excluding the repeticions | Excel Discussion (Misc queries) | |||
Linear Mode
