test itteration of array formula

  • Thread starter Thread starter ICE9
  • Start date Start date
I

ICE9

Is there any way to test a particular itteration of an array formula?

When you use "Evaluate Formula" on an array formula, it starts with th
1st row and goes through each one. If one row causes an error, i
populates the error all the way to the end value. I would like to b
able to determine which itteration of the formula causes the error, o
even specify which itteration of the formula i would like to evaluate
 
ICE9 wrote...
Is there any way to test a particular itteration of an array formula?

When you use "Evaluate Formula" on an array formula, it starts
with the 1st row and goes through each one. If one row
causes an error, it populates the error all the way to the end
value. I would like to be able to determine which itteration of
the formula causes the error, or even specify which itteration of
the formula i would like to evaluate.
Click to expand...

Show your formula. If you mean something like

=AVERAGE(IF(A1:A100="X",B1:B100))

throwing errors if either A1:A100 or B1:B100 contain errors, then th
first error in either column could be found using

=MATCH(TRUE,ISERROR(A1:A100),0)

which would return the row index of the topmost cell containing a
error in A1:A100 or #N/A if there were no error in A1:A100
 
Well, my formula is somewhat more tricky since it's an array formula.

{=SUM(IF(mscSite="msc",IF(mscOpenedBy="CHD",IF(mscDay=23,1,0),0),0))}

The named lists mscSite, mscOpenedBy, and mscDay are columns in
series of over 3,000 records. Excel runs the formula once for ever
line and sums the total number of records in which all three condition
were true.

Alternately some of the formulas use boolean math to achieve the sam
goal.

{=SUM( (IF(acdSite=B6,1,0)) * (
IF(ISNUMBER(acdDay),IF(acdDay=$H$2,1,0),0) )
(IF(ISNUMBER(acdOffered),acdOffered,0)) )}

In this example each condition individually represents it's numeri
boolean value which is then multiplied. Either way, you get a 1 fo
each row in which all values are true, then they are added together.
You can see here where i've added the ISNUMBER function to fix a
error.

In the second example it might be easier to separate each argument an
test individually, but this is still rather difficult. Perhaps what w
really need is an improvement of the "Evaluate Formula" which allows th
user to specify a row number for the array function, or to obtain a ro
number of the error.

Alternately, it would be nice if the array function had an option t
exclude errors
 
ICE9 > said:
Well, my formula is somewhat more tricky since it's an array formula.

{=SUM(IF(mscSite="msc",IF(mscOpenedBy="CHD",IF(mscDay=23,1,0),0),0))}

The named lists mscSite, mscOpenedBy, and mscDay are columns in a
series of over 3,000 records. Excel runs the formula once for every
line and sums the total number of records in which all three conditions
were true.
Click to expand...

You could replace the array formyla above with the nonarray formula

=SUMPRODUCT((mscSite="msc")*(mscOpenedBy="CHD")*(mscDay=23))

Alternately some of the formulas use boolean math to achieve the same
goal.

{=SUM( (IF(acdSite=B6,1,0)) * (
IF(ISNUMBER(acdDay),IF(acdDay=$H$2,1,0),0) ) *
(IF(ISNUMBER(acdOffered),acdOffered,0)) )}
Click to expand...
....

And this with

=SUMPRODUCT((acdSite=B6)*(acdDay=$H$2),acdOffered)

which will return the same result if H2 is numeric.
In the second example it might be easier to separate each argument and
test individually, but this is still rather difficult. Perhaps what we
really need is an improvement of the "Evaluate Formula" which allows the
user to specify a row number for the array function, or to obtain a row
number of the error.
Click to expand...

If your table were sorted in ascending order, you could use OFFSET and MATCH
to isolate the records matching given criteria, but this may not result in
much recalc efficiency gain.

Anyway, you have conditional counting and summing formulas. If they return
errors, then given your criteria the only way you'd get errors would be
either when there were error values in these ranges or when the ranges
weren't the same size. What exact errors are you getting?
 
Back
Top