Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Can an "IF" formula return a blank cell, not a zero?
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
=IF(A1<12;"";A1*2)
HTH. Best wishes Harald "MacPadana" wrote in message ... Can an "IF" formula return a blank cell, not a zero? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Try this:
=IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
That's what I thought at first but when I try to run discriptive statistics
on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
It looks like your "discriptive statistics" don't like blank cells. Explain
what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
This is the formula I am using:
=IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Your formulas are constructed to return an appearance of being empty by
using "", which is actually a zero length string. The important word here being "string", which means TEXT. Perhaps your best choice is to use numeric zero, and eliminate its display by: From the Menu bar, <Tools <Options <Display tab, and *uncheck* "zero values" under Window Options. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi "MacPadana" ezt ķrta: Can an "IF" formula return a blank cell, not a zero? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
The only problem is that Excel will use the zeros in calculating the
statistics making them completely inaccurate. I appreciate everyone's input. This seams to be a tougher question than I originally thought. Mac "RagDyeR" wrote: Your formulas are constructed to return an appearance of being empty by using "", which is actually a zero length string. The important word here being "string", which means TEXT. Perhaps your best choice is to use numeric zero, and eliminate its display by: From the Menu bar, <Tools <Options <Display tab, and *uncheck* "zero values" under Window Options. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi "MacPadana" ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
But in that case, the invisible zero would be fed into the OP's statistical
calculation, which may well not be what he wants. -- David Biddulph "RagDyeR" wrote in message ... Your formulas are constructed to return an appearance of being empty by using "", which is actually a zero length string. The important word here being "string", which means TEXT. Perhaps your best choice is to use numeric zero, and eliminate its display by: From the Menu bar, <Tools <Options <Display tab, and *uncheck* "zero values" under Window Options. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi "MacPadana" ezt ķrta: Can an "IF" formula return a blank cell, not a zero? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
I see a couple of options.
1. Create a range which has just the cells greater than 70. For example, filter out the cells less than 70, copy that range, and then do the statistics on the new range. 2. Change your statistical calculations to exclude values below 70. For example, to calculate the mean of all the values in column C greater than 70, you can use: =SUMIF(C:C,"=70")/COUNTIF(C:C,"=70") As statistics aren't my strong suit, I won't be able to help you on all the formulas. However, there's lots of people in this group who can. Just post another question like "How do I calculate the standard deviation of column C, excluding values below 70?". When you post, make sure you identify what version of Excel you are using, because it will make a difference in the functions available. Regards, Fred "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Perfect solution: filter, copy, paste. Thanks, Fred and everyone else.
Mac "Fred Smith" wrote: I see a couple of options. 1. Create a range which has just the cells greater than 70. For example, filter out the cells less than 70, copy that range, and then do the statistics on the new range. 2. Change your statistical calculations to exclude values below 70. For example, to calculate the mean of all the values in column C greater than 70, you can use: =SUMIF(C:C,"=70")/COUNTIF(C:C,"=70") As statistics aren't my strong suit, I won't be able to help you on all the formulas. However, there's lots of people in this group who can. Just post another question like "How do I calculate the standard deviation of column C, excluding values below 70?". When you post, make sure you identify what version of Excel you are using, because it will make a difference in the functions available. Regards, Fred "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Sorry to have to revisit this one. Now I have a range of cells I need to
include in a chart. As long as the cells are blank, the line on the graph stops, which is what I want. However, if I put in the formula =IF(B30,(C3/B3),""), Excell interprates "" as zero and the line on my chart drops to zero. I need it to calculate a value as soon as someone enters a value in B3, otherwise, it should treat it exactly as if it were blank. Any suggestions wil be eagerly tested. Mac "MacPadana" wrote: Perfect solution: filter, copy, paste. Thanks, Fred and everyone else. Mac "Fred Smith" wrote: I see a couple of options. 1. Create a range which has just the cells greater than 70. For example, filter out the cells less than 70, copy that range, and then do the statistics on the new range. 2. Change your statistical calculations to exclude values below 70. For example, to calculate the mean of all the values in column C greater than 70, you can use: =SUMIF(C:C,"=70")/COUNTIF(C:C,"=70") As statistics aren't my strong suit, I won't be able to help you on all the formulas. However, there's lots of people in this group who can. Just post another question like "How do I calculate the standard deviation of column C, excluding values below 70?". When you post, make sure you identify what version of Excel you are using, because it will make a difference in the functions available. Regards, Fred "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi €˛MacPadana€¯ ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Change =IF(B30,(C3/B3),"") to =IF(B30,C3/B3,NA())
Note that you are rejecting negative values of B3. If you are merely trying to deal with blank in B3, or to avoid a divide by zero, you might prefer =IF(B3<0,C3/B3,NA()) -- David Biddulph MacPadana wrote: Sorry to have to revisit this one. Now I have a range of cells I need to include in a chart. As long as the cells are blank, the line on the graph stops, which is what I want. However, if I put in the formula =IF(B30,(C3/B3),""), Excell interprates "" as zero and the line on my chart drops to zero. I need it to calculate a value as soon as someone enters a value in B3, otherwise, it should treat it exactly as if it were blank. Any suggestions wil be eagerly tested. Mac "MacPadana" wrote: Perfect solution: filter, copy, paste. Thanks, Fred and everyone else. Mac "Fred Smith" wrote: I see a couple of options. 1. Create a range which has just the cells greater than 70. For example, filter out the cells less than 70, copy that range, and then do the statistics on the new range. 2. Change your statistical calculations to exclude values below 70. For example, to calculate the mean of all the values in column C greater than 70, you can use: =SUMIF(C:C,"=70")/COUNTIF(C:C,"=70") As statistics aren't my strong suit, I won't be able to help you on all the formulas. However, there's lots of people in this group who can. Just post another question like "How do I calculate the standard deviation of column C, excluding values below 70?". When you post, make sure you identify what version of Excel you are using, because it will make a difference in the functions available. Regards, Fred "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi "MacPadana" ezt ķrta: Can an "IF" formula return a blank cell, not a zero? |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Thanks David,
That works. I now get #N/A when ther is nothing in B3 but I can hide that with Conditional Formatting. My graph looks exactly the way I was hoping it would. Great reply. Thanks again, Mac "David Biddulph" wrote: Change =IF(B30,(C3/B3),"") to =IF(B30,C3/B3,NA()) Note that you are rejecting negative values of B3. If you are merely trying to deal with blank in B3, or to avoid a divide by zero, you might prefer =IF(B3<0,C3/B3,NA()) -- David Biddulph MacPadana wrote: Sorry to have to revisit this one. Now I have a range of cells I need to include in a chart. As long as the cells are blank, the line on the graph stops, which is what I want. However, if I put in the formula =IF(B30,(C3/B3),""), Excell interprates "" as zero and the line on my chart drops to zero. I need it to calculate a value as soon as someone enters a value in B3, otherwise, it should treat it exactly as if it were blank. Any suggestions wil be eagerly tested. Mac "MacPadana" wrote: Perfect solution: filter, copy, paste. Thanks, Fred and everyone else. Mac "Fred Smith" wrote: I see a couple of options. 1. Create a range which has just the cells greater than 70. For example, filter out the cells less than 70, copy that range, and then do the statistics on the new range. 2. Change your statistical calculations to exclude values below 70. For example, to calculate the mean of all the values in column C greater than 70, you can use: =SUMIF(C:C,"=70")/COUNTIF(C:C,"=70") As statistics aren't my strong suit, I won't be able to help you on all the formulas. However, there's lots of people in this group who can. Just post another question like "How do I calculate the standard deviation of column C, excluding values below 70?". When you post, make sure you identify what version of Excel you are using, because it will make a difference in the functions available. Regards, Fred "MacPadana" wrote in message ... This is the formula I am using: =IF(C9=70,C9,"") I want to populate the cells in a column with only the values in Column C that are greater than or equal to 70. Once I have that populated I want to use the Descriptive Statistics, Data Analysis tool to get Mean, Median, Mode , Standard Deviation etc. on the data in the new column. As soon as there is a cell in column C below 70, though, whatever is returned in the new column prevents Excel from being able to calculate the statistics. If there is an easier way to go about it, I'd be happy to hear that too. Thanks "Fred Smith" wrote: It looks like your "discriptive statistics" don't like blank cells. Explain what discriptive statistics are and someone will be sure to help you. Posting the formulas you are using is the best way to get help. Regards, Fred. "MacPadana" wrote in message ... That's what I thought at first but when I try to run discriptive statistics on a range of cells that includes that, I get a message that says "Input range contains non-numeric data". Any other Ideas? "Stefi" wrote: Try this: =IF(condition,"",result_if false) Regards, Stefi "MacPadana" ezt Ć*rta: Can an "IF" formula return a blank cell, not a zero? |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF function
Glad it worked for you.
-- David Biddulph MacPadana wrote: Thanks David, That works. I now get #N/A when ther is nothing in B3 but I can hide that with Conditional Formatting. My graph looks exactly the way I was hoping it would. Great reply. Thanks again, Mac "David Biddulph" wrote: Change =IF(B30,(C3/B3),"") to =IF(B30,C3/B3,NA()) Note that you are rejecting negative values of B3. If you are merely trying to deal with blank in B3, or to avoid a divide by zero, you might prefer =IF(B3<0,C3/B3,NA()) -- David Biddulph MacPadana wrote: Sorry to have to revisit this one. Now I have a range of cells I need to include in a chart. As long as the cells are blank, the line on the graph stops, which is what I want. However, if I put in the formula =IF(B30,(C3/B3),""), Excell interprates "" as zero and the line on my chart drops to zero. I need it to calculate a value as soon as someone enters a value in B3, otherwise, it should treat it exactly as if it were blank. Any suggestions wil be eagerly tested. .... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK function not working when cell is blank dut to function re | Excel Discussion (Misc queries) | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |