| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show Null As %0
Hello All:
I have two cells A1 and A2 . Am calculating Percentile in cell A2. The input is Cell A1. Please don't get hung-up on the formula in Cell A2. it works for what I am trying to do the problem is that 0 is also calculated as %100 which is correct( we are capturing the number of errors - so 0 error is %100 which is no error). The Problem is that if cell A1 is null this also reads as %100 in A2, which creates a problem. Is there any way to have the formula read null as null, so the percentile reflect %0. A1 = User input A2 =(100-(D188*0.45))/100 Ardy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Null As %0
Try this:
=((100-(A1*0.45))/100)*(A1<"") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ardy" wrote in message ups.com... Hello All: I have two cells A1 and A2 . Am calculating Percentile in cell A2. The input is Cell A1. Please don't get hung-up on the formula in Cell A2. it works for what I am trying to do the problem is that 0 is also calculated as %100 which is correct( we are capturing the number of errors - so 0 error is %100 which is no error). The Problem is that if cell A1 is null this also reads as %100 in A2, which creates a problem. Is there any way to have the formula read null as null, so the percentile reflect %0. A1 = User input A2 =(100-(D188*0.45))/100 Ardy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Null As %0
One way:
=IF(A1="",0,1-D188*0.0045) In article . com, "Ardy" wrote: Hello All: I have two cells A1 and A2 . Am calculating Percentile in cell A2. The input is Cell A1. Please don't get hung-up on the formula in Cell A2. it works for what I am trying to do the problem is that 0 is also calculated as %100 which is correct( we are capturing the number of errors - so 0 error is %100 which is no error). The Problem is that if cell A1 is null this also reads as %100 in A2, which creates a problem. Is there any way to have the formula read null as null, so the percentile reflect %0. A1 = User input A2 =(100-(D188*0.45))/100 Ardy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Null As %0
Thnaks Both Method Works OK,
I used =((100-(K108*0.45))/100)*(K108<""), Could you explain (K108<"") is this another way of saying IF null Ardy JE McGimpsey wrote: One way: =IF(A1="",0,1-D188*0.0045) In article . com, "Ardy" wrote: Hello All: I have two cells A1 and A2 . Am calculating Percentile in cell A2. The input is Cell A1. Please don't get hung-up on the formula in Cell A2. it works for what I am trying to do the problem is that 0 is also calculated as %100 which is correct( we are capturing the number of errors - so 0 error is %100 which is no error). The Problem is that if cell A1 is null this also reads as %100 in A2, which creates a problem. Is there any way to have the formula read null as null, so the percentile reflect %0. A1 = User input A2 =(100-(D188*0.45))/100 Ardy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Null As %0
Its just a Boolean factor. if K108 is blank, then the factor is zero and so
is the final result. -- Gary's Student gsnu200702 "Ardy" wrote: Thnaks Both Method Works OK, I used =((100-(K108*0.45))/100)*(K108<""), Could you explain (K108<"") is this another way of saying IF null Ardy JE McGimpsey wrote: One way: =IF(A1="",0,1-D188*0.0045) In article . com, "Ardy" wrote: Hello All: I have two cells A1 and A2 . Am calculating Percentile in cell A2. The input is Cell A1. Please don't get hung-up on the formula in Cell A2. it works for what I am trying to do the problem is that 0 is also calculated as %100 which is correct( we are capturing the number of errors - so 0 error is %100 which is no error). The Problem is that if cell A1 is null this also reads as %100 in A2, which creates a problem. Is there any way to have the formula read null as null, so the percentile reflect %0. A1 = User input A2 =(100-(D188*0.45))/100 Ardy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Null As %0
The (K108<"") term evaluates to a boolean TRUE/FALSE. When TRUE/FALSE
is used in a math operation, here *, XL coerces TRUE/FALSE to 1/0. So if the term evaluates to FALSE, the first term will be multiplied by zero, otherwise it is multiplied by 1. You could do the same thing with the formula I gave: =(1-K108*0.0045)*(K108<"") In article .com, "Ardy" wrote: Could you explain (K108<"") is this another way of saying IF null |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Null As %0
Thanks Guys......
It sure helps a lot....... Ardy JE McGimpsey wrote: The (K108<"") term evaluates to a boolean TRUE/FALSE. When TRUE/FALSE is used in a math operation, here *, XL coerces TRUE/FALSE to 1/0. So if the term evaluates to FALSE, the first term will be multiplied by zero, otherwise it is multiplied by 1. You could do the same thing with the formula I gave: =(1-K108*0.0045)*(K108<"") In article .com, "Ardy" wrote: Could you explain (K108<"") is this another way of saying IF null |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros Don't Show On Commands List and 'Normal.dot' | Excel Worksheet Functions | |||
| How do I get items from sheet 3 to show up on sheet 1 and 2? | Excel Worksheet Functions | |||
| how show sum & avg same time in status bar? | Excel Discussion (Misc queries) | |||
| Preventing NULL showing as 0 on a pivot chart using Office Web Component. | Charts and Charting in Excel | |||
| Show values from other sheet | Excel Worksheet Functions | |||
Linear Mode
