Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore cells that are blank
Hello, I am using the following formula to count the number of occurrences of
my data being out of tolerance. My column of data (J46:J522) contains numbers and blank cells. My upper and lower tolerances are contained in cells L3 and L4. Therefore this equation is counting the number of data results less than the lower tolerance and higher than the high tolerance. This formula works great when L3 and L4 are bilateral (i.e. +/-.001). But a problem arises when L3 and L4 are unilateral (i.e. +.001/+.002). This is because the formula is treating blank cells as 0. Is there a way to make this formula ignore the blank cells? {=SUM(IF((J46:J522<L4)+(J46:J522L3),1,0))} |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore cells that are blank
Try this (normally entered):
=SUMPRODUCT(--(J46:J522<""),--(J46:J522L3),--(J46:J522<L4)) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Hello, I am using the following formula to count the number of occurrences of my data being out of tolerance. My column of data (J46:J522) contains numbers and blank cells. My upper and lower tolerances are contained in cells L3 and L4. Therefore this equation is counting the number of data results less than the lower tolerance and higher than the high tolerance. This formula works great when L3 and L4 are bilateral (i.e. +/-.001). But a problem arises when L3 and L4 are unilateral (i.e. +.001/+.002). This is because the formula is treating blank cells as 0. Is there a way to make this formula ignore the blank cells? {=SUM(IF((J46:J522<L4)+(J46:J522L3),1,0))} |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore cells that are blank
For some reason your equation returns a value of zero always, regardless of
what numbers I have in L3 and L4. Do you know why this might happen? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore cells that are blank
Interestingly, this one DOES work:
=SUMPRODUCT(--(J46:J522<""),--(J46:J522<L4))+SUMPRODUCT(--(J46:J522<""),--(J46:J522L3)) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignore cells that are blank
Your array formula is written as an "or" conditional test.
J46:J522<L4 OR J46:J522L3 The formula I suggested is written as an "and" conditional test: J46:J522L3 AND J46:J522<L4 The formula that you got to work is essentially the same as the "or" version but it's spread across two separate functions. This one will do the same thing: =SUMPRODUCT(--(J46:J522<""),--((J46:J522<L4)+(J46:J522L3)0)) -- Biff Microsoft Excel MVP "Robert" wrote in message ... Interestingly, this one DOES work: =SUMPRODUCT(--(J46:J522<""),--(J46:J522<L4))+SUMPRODUCT(--(J46:J522<""),--(J46:J522L3)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignore Blank or 0 cells | Excel Worksheet Functions | |||
Ignore blank cells | Excel Discussion (Misc queries) | |||
ignore blank cells | Excel Discussion (Misc queries) | |||
How to ignore blank cells | Charts and Charting in Excel | |||
how do you ignore blank cells | Excel Discussion (Misc queries) |