Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
I have a spreadsheet where in some cells I want the cell to show
nothing if a condition is not met: For example, in cell A1: = IF (a = b, a + b, "") If in A2 when a<b, I check to see if this value is greater than 0, it returns TRUE: = A1 0 I can even simplify A1 just to be: = "" and the it still returns that this cell 0. Can someone please explain and maybe offer an alternative way to display nothing in a cell that will register as a 0? Thanks. John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
TEXT will always evaluate to be greater than ANY number. What you can do is
test the blank/empty cell to see if it actually contains a number: =AND(ISNUMBER(A1),A10) Biff wrote in message ups.com... I have a spreadsheet where in some cells I want the cell to show nothing if a condition is not met: For example, in cell A1: = IF (a = b, a + b, "") If in A2 when a<b, I check to see if this value is greater than 0, it returns TRUE: = A1 0 I can even simplify A1 just to be: = "" and the it still returns that this cell 0. Can someone please explain and maybe offer an alternative way to display nothing in a cell that will register as a 0? Thanks. John |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
The only catch is that this is all part of a complicated SUM array to
count if two conditions are met: ={SUM(IF('Raw Data'!$D$3:$D$500="502",IF('Raw Data'!$P$3:$P$500180,1,0),0))} So in this case, if a cell in Col D = 502 AND a cell in Col P 180 it would add 1. Any suggestions on how to do this if I have blank cells (i.e. "") in column P if there is no number? I don't really want to add another IF statement to the array. Thanks... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
Try this (normally entered, not an array formula):
=SUMPRODUCT(--('Raw Data'!$D$3:$D$500="502"),--(ISNUMBER('Raw Data'!$P$3:$P$500)),--('Raw Data'!$P$3:$P$500180)) Are you sure you want this: 'Raw Data'!$D$3:$D$500="502" Enclosing a number in quotes makes it a TEXT value and not a NUMERIC value. Try removing the quotes from around 502. Biff wrote in message oups.com... The only catch is that this is all part of a complicated SUM array to count if two conditions are met: ={SUM(IF('Raw Data'!$D$3:$D$500="502",IF('Raw Data'!$P$3:$P$500180,1,0),0))} So in this case, if a cell in Col D = 502 AND a cell in Col P 180 it would add 1. Any suggestions on how to do this if I have blank cells (i.e. "") in column P if there is no number? I don't really want to add another IF statement to the array. Thanks... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
Yes, the "502" is actually a text field.
SUMPRODUCT seems like an easy way to handle multiple conditions with out getting into arrays. What does the "--" mean? Does that convert TRUE from -1 to 1? Can "--" used in other formulas / VBA? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
SUMPRODUCT seems like an easy way to handle multiple conditions with
out getting into arrays. Yes, it is. Sumproduct is extemely useful and versatile. What does the "--" mean? Does that convert TRUE from -1 to 1? Yes, but it's a 2 step process. -TRUE = -1 then --1 = 1. Basically, it converts TRUE to 1 and FALSE to 0. Can "--" used in other formulas / VBA? I'm not sure about VBA but it can be used in any formula where you need/want to convert TEXT numbers/logicals into NUMERIC values. See this for a detailed explanation: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Biff wrote in message oups.com... Yes, the "502" is actually a text field. SUMPRODUCT seems like an easy way to handle multiple conditions with out getting into arrays. What does the "--" mean? Does that convert TRUE from -1 to 1? Can "--" used in other formulas / VBA? Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why does blank text equal 0?
Looks like it works in VBA, but I've never used it in a practical application
so I don't know what limitations or pitfalls may exist. In VBA, however, TRUE = -1, which may be what prompted the OP's question: What does the "--" mean? Does that convert TRUE from -1 to 1? Sub test() x = --IsNumeric(5) MsgBox x End Sub "Biff" wrote: SUMPRODUCT seems like an easy way to handle multiple conditions with out getting into arrays. Yes, it is. Sumproduct is extemely useful and versatile. What does the "--" mean? Does that convert TRUE from -1 to 1? Yes, but it's a 2 step process. -TRUE = -1 then --1 = 1. Basically, it converts TRUE to 1 and FALSE to 0. Can "--" used in other formulas / VBA? I'm not sure about VBA but it can be used in any formula where you need/want to convert TEXT numbers/logicals into NUMERIC values. See this for a detailed explanation: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Biff wrote in message oups.com... Yes, the "502" is actually a text field. SUMPRODUCT seems like an easy way to handle multiple conditions with out getting into arrays. What does the "--" mean? Does that convert TRUE from -1 to 1? Can "--" used in other formulas / VBA? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple formatting in text cell blank it out! | Excel Discussion (Misc queries) | |||
How change dimensions of data label text box in pie chart? | Charts and Charting in Excel | |||
Capitalize Text if 2 lists are equal | New Users to Excel | |||
How do I clear blank space at the top of a word-wrapped text cell? | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |