| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Strange" array formula issue
hello -
Thanks to these Groups, I've become quite proficient at using array formulas to evaluate fairly complex calculations. However, I've run into a small issue that I just cannot understand and it seems like it shouldn't be so difficult... This array formula works fine: ("week" is a Named cell) {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))} This array formula DOES NOT work fine: {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))} The difference btw the two formulas is this: ($J$3:$J$11000="C") The strange part that this formula DOES work: =COUNTIF($J$3:$J$11000,"C") Is there some fundamental difference in evaluating a range using an array formula vs the COUNTIF function? I've tried evaluating the 'bad' part of the array formula to a VALUE and to TEXT, but no change... Any ideas? TIA, Ray |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Strange" array formula issue
It works for me, I think you must have bad data in column J.
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in message oups.com... hello - Thanks to these Groups, I've become quite proficient at using array formulas to evaluate fairly complex calculations. However, I've run into a small issue that I just cannot understand and it seems like it shouldn't be so difficult... This array formula works fine: ("week" is a Named cell) {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))} This array formula DOES NOT work fine: {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))} The difference btw the two formulas is this: ($J$3:$J$11000="C") The strange part that this formula DOES work: =COUNTIF($J$3:$J$11000,"C") Is there some fundamental difference in evaluating a range using an array formula vs the COUNTIF function? I've tried evaluating the 'bad' part of the array formula to a VALUE and to TEXT, but no change... Any ideas? TIA, Ray |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Strange" array formula issue
Thanks for the response, Bob ...
What do you mean by 'bad data'? And if it was bad data, why would the SUMIF formula work ok? It's evaluating against the same critieria (cell.value="C") .... On Jan 25, 8:13 am, "Bob Phillips" wrote: It works for me, I think you must have bad data in column J. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in ooglegroups.com... hello - Thanks to these Groups, I've become quite proficient at using array formulas to evaluate fairly complex calculations. However, I've run into a small issue that I just cannot understand and it seems like it shouldn't be so difficult... This array formula works fine: ("week" is a Named cell) {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))} This array formula DOES NOT work fine: {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))} The difference btw the two formulas is this: ($J$3:$J$11000="C") The strange part that this formula DOES work: =COUNTIF($J$3:$J$11000,"C") Is there some fundamental difference in evaluating a range using an array formula vs the COUNTIF function? I've tried evaluating the 'bad' part of the array formula to a VALUE and to TEXT, but no change... Any ideas? TIA, Ray |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Strange" array formula issue
Maybe so, but whereas SUMIF ignores those errors, SUMPRODUCT does not. By
bad data, I mean a cell with #VALUE or #REF within it. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in message oups.com... Thanks for the response, Bob ... What do you mean by 'bad data'? And if it was bad data, why would the SUMIF formula work ok? It's evaluating against the same critieria (cell.value="C") .... On Jan 25, 8:13 am, "Bob Phillips" wrote: It works for me, I think you must have bad data in column J. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in ooglegroups.com... hello - Thanks to these Groups, I've become quite proficient at using array formulas to evaluate fairly complex calculations. However, I've run into a small issue that I just cannot understand and it seems like it shouldn't be so difficult... This array formula works fine: ("week" is a Named cell) {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))} This array formula DOES NOT work fine: {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))} The difference btw the two formulas is this: ($J$3:$J$11000="C") The strange part that this formula DOES work: =COUNTIF($J$3:$J$11000,"C") Is there some fundamental difference in evaluating a range using an array formula vs the COUNTIF function? I've tried evaluating the 'bad' part of the array formula to a VALUE and to TEXT, but no change... Any ideas? TIA, Ray |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Strange" array formula issue
I'm not using SUMPRODUCT, just SUM as an array formula -- OR, does that
make them the same? And even if there are 'errors' (ie #VALUE or #REF), doesn't the first part of my formula ( ie IF(ISERROR... ) take care of those errors? I've used this logic before on other arrays containing 'bad data' and it seemed to work fine. I just can't figure out how this situation is different... On Jan 25, 9:51 am, "Bob Phillips" wrote: Maybe so, but whereas SUMIF ignores those errors, SUMPRODUCT does not. By bad data, I mean a cell with #VALUE or #REF within it. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in ooglegroups.com... Thanks for the response, Bob ... What do you mean by 'bad data'? And if it was bad data, why would the SUMIF formula work ok? It's evaluating against the same critieria (cell.value="C") .... On Jan 25, 8:13 am, "Bob Phillips" wrote: It works for me, I think you must have bad data in column J. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in ooglegroups.com... hello - Thanks to these Groups, I've become quite proficient at using array formulas to evaluate fairly complex calculations. However, I've run into a small issue that I just cannot understand and it seems like it shouldn't be so difficult... This array formula works fine: ("week" is a Named cell) {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))} This array formula DOES NOT work fine: {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))} The difference btw the two formulas is this: ($J$3:$J$11000="C") The strange part that this formula DOES work: =COUNTIF($J$3:$J$11000,"C") Is there some fundamental difference in evaluating a range using an array formula vs the COUNTIF function? I've tried evaluating the 'bad' part of the array formula to a VALUE and to TEXT, but no change... Any ideas? TIA, Ray |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"Strange" array formula issue
Alright, SUM(IF then <g, it is still not SUMIF. They have been coded by
different teams, and so maybe the quality control wasn't quite there. And the error trapping won't catch it if it tests column M and the error is in J. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in message ups.com... I'm not using SUMPRODUCT, just SUM as an array formula -- OR, does that make them the same? And even if there are 'errors' (ie #VALUE or #REF), doesn't the first part of my formula ( ie IF(ISERROR... ) take care of those errors? I've used this logic before on other arrays containing 'bad data' and it seemed to work fine. I just can't figure out how this situation is different... On Jan 25, 9:51 am, "Bob Phillips" wrote: Maybe so, but whereas SUMIF ignores those errors, SUMPRODUCT does not. By bad data, I mean a cell with #VALUE or #REF within it. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in ooglegroups.com... Thanks for the response, Bob ... What do you mean by 'bad data'? And if it was bad data, why would the SUMIF formula work ok? It's evaluating against the same critieria (cell.value="C") .... On Jan 25, 8:13 am, "Bob Phillips" wrote: It works for me, I think you must have bad data in column J. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Ray" wrote in ooglegroups.com... hello - Thanks to these Groups, I've become quite proficient at using array formulas to evaluate fairly complex calculations. However, I've run into a small issue that I just cannot understand and it seems like it shouldn't be so difficult... This array formula works fine: ("week" is a Named cell) {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $G$3:$G$11000=($A9&week)))} This array formula DOES NOT work fine: {=SUM(IF(ISERROR($M$3:$M$11000),0,$M$3:$M$11000)*( $J$3:$J$11000="C")*($G$3:$G$11000=($A9&week)))} The difference btw the two formulas is this: ($J$3:$J$11000="C") The strange part that this formula DOES work: =COUNTIF($J$3:$J$11000,"C") Is there some fundamental difference in evaluating a range using an array formula vs the COUNTIF function? I've tried evaluating the 'bad' part of the array formula to a VALUE and to TEXT, but no change... Any ideas? TIA, Ray |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula issue with the "OR" command | Excel Worksheet Functions | |||
| "Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
| How do I use "offset" function in "array formula"? | Excel Discussion (Misc queries) | |||
| Listbox header inside VBA (Array("Head1", "Head2", ...) | Excel Programming | |||
| If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
Linear Mode
