Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate a range of cells for true or false condition (Div/0)
I want to check a range of cells for an all zero condition to preclude a
divide by zero error. If any of the cells (in the range) contain non-zero data then proceed with the remaining calculation. If all the cells (in the range) are zeros, then stop and optionally present either a meaning full message (my composition) or just leave blank (""). =IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"") -- Bill Billmire |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate a range of cells for true or false condition (Div/0)
You might try something like the following. Be warned, though, it will
#VALUE out if you have non-numeric text in a cell. =IF(SUM(ABS(A1:A20))=0, "All zeros","at least one non-zero") This adds up the absolute values of A1:A20 and if that SUM is 0 then all elements in A1:A20 are 0. If any cell is non-zero, the SUM will be non-zero. This uses absolute values to sum up. This will prevent negative and positive values cancelling out one another. This is an array formula so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Bill Billmire" wrote in message ... I want to check a range of cells for an all zero condition to preclude a divide by zero error. If any of the cells (in the range) contain non-zero data then proceed with the remaining calculation. If all the cells (in the range) are zeros, then stop and optionally present either a meaning full message (my composition) or just leave blank (""). =IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"") -- Bill Billmire |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate a range of cells for true or false condition (Div/0)
Thanks Chip for the quick response...
Unfortunately the range of data that I want to check for a zero condition is alpha. For example column "A" is a range of numbers, column "B" is a code letter "A-Z" that will represent a category for the corresponding number. I need to test column "B" for zero entrys to preclude a #DIV/0 error for the specific "code letter". Current Formula I'm working with (see below) - except the hard coded "20" limits the functions ability to scale when the data grows without needing to edit the formula as the data grows. =IF(COUNTBLANK(B1:B20)=20,"Empty",SUMIF(B1:B20,"E" ,A1:A20)/COUNTIF(B1:B20,"E")) Bill Billmire - "Chip Pearson" wrote: You might try something like the following. Be warned, though, it will #VALUE out if you have non-numeric text in a cell. =IF(SUM(ABS(A1:A20))=0, "All zeros","at least one non-zero") This adds up the absolute values of A1:A20 and if that SUM is 0 then all elements in A1:A20 are 0. If any cell is non-zero, the SUM will be non-zero. This uses absolute values to sum up. This will prevent negative and positive values cancelling out one another. This is an array formula so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Bill Billmire" wrote in message ... I want to check a range of cells for an all zero condition to preclude a divide by zero error. If any of the cells (in the range) contain non-zero data then proceed with the remaining calculation. If all the cells (in the range) are zeros, then stop and optionally present either a meaning full message (my composition) or just leave blank (""). =IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"") -- Bill Billmire |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Evaluate a range of cells for true or false condition (Div/0)
OK - I think I got it...
This logic counts the number of blank cells in the range and compares that to the total number of cells in the range. So if 20=20, [true] = "Empty", else [false] and calculate the sumif()/countif(). Tested and no more #DIV/0... And its scalable... =IF(COUNTBLANK(B1:B20)=ROWS(B1:B20),"Empty",SUMIF( B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E")) "Bill Billmire" wrote: Thanks Chip for the quick response... Unfortunately the range of data that I want to check for a zero condition is alpha. For example column "A" is a range of numbers, column "B" is a code letter "A-Z" that will represent a category for the corresponding number. I need to test column "B" for zero entrys to preclude a #DIV/0 error for the specific "code letter". Current Formula I'm working with (see below) - except the hard coded "20" limits the functions ability to scale when the data grows without needing to edit the formula as the data grows. =IF(COUNTBLANK(B1:B20)=20,"Empty",SUMIF(B1:B20,"E" ,A1:A20)/COUNTIF(B1:B20,"E")) Bill Billmire - "Chip Pearson" wrote: You might try something like the following. Be warned, though, it will #VALUE out if you have non-numeric text in a cell. =IF(SUM(ABS(A1:A20))=0, "All zeros","at least one non-zero") This adds up the absolute values of A1:A20 and if that SUM is 0 then all elements in A1:A20 are 0. If any cell is non-zero, the SUM will be non-zero. This uses absolute values to sum up. This will prevent negative and positive values cancelling out one another. This is an array formula so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula in the formula bar enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx for more info about array formulas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Bill Billmire" wrote in message ... I want to check a range of cells for an all zero condition to preclude a divide by zero error. If any of the cells (in the range) contain non-zero data then proceed with the remaining calculation. If all the cells (in the range) are zeros, then stop and optionally present either a meaning full message (my composition) or just leave blank (""). =IF(logical_test,SUMIF(B1:B20,"E",A1:A20)/COUNTIF(B1:B20,"E"),"") -- Bill Billmire |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Percentage of True (or False) in a range | Excel Discussion (Misc queries) | |||
What's the best way to toggle between true and false in Excel? Hiall, My excel work involves a lot of toggling between true and false (booleantypes) ... and it's very repetitive... Is there a way to select a bunch ofcells, and press a key short-cu | Excel Discussion (Misc queries) | |||
Marking a range True or False based on a single Checkbox | Excel Programming | |||
Hiding rows in a range based on TRUE/FALSE value in each row | Excel Discussion (Misc queries) | |||
Formula: If 2 values (in a range of six) are >3 then TRUE, FALSE | Excel Discussion (Misc queries) |