I have an array formula which I am attempting to have ignore some bad data in cells which is usually text. Right now, as long as I have text in a few of these cells it is throwing a #VALUE at the end of evaluation. I am using the "Show Calculation Steps" feature to step through the evaluation of the formula and it brought me to the question of "How does excel iterate through multiple rows when it is processing a formula?"
Here is my formula
=AVERAGE(IF(AND(Categories1[Column1]="T&M",NOT(ISERROR(Categories1[Check]))),PRODUCT(QUOTIENT(IF(ISERROR(Categories1[PO Amount - Labor]),0,Categories1[PO Amount - Labor]),IF(ISERROR(Categories1[SUB Daily Rate]), 1, Categories1[SUB Daily Rate])),8),""))
Having a programming background what I would expect to happen here is that the formula would run like a FOREACH loop on each row of the range. But, the way the Formula evaluator is displaying it's steps makes me question that. I will try to explain below as best I can.
It starts by performing as I would expect and evaluating whether the cells in Column1 = T&M or not. So I get a set of TRUE's and FALSE's
1.jpg
Clicking "Evaluate" again shows that ISERROR finds two #VALUE errors and the rest of the data is 0 or not an error.
2.jpg
ISERROR then does what I would expect and evaluates to TRUE, TRUE and then all FALSE's
3.jpg
That get's NOTed to be FALSE, FALSE and then a bunch of TRUE's...again, what I would expect
4.jpg
This next step is what I didnt expect. I thought it would then iterate through each row and take the combination of a (TRUE, FALSE) for row 1 and skip it. Then the combination of (TRUE, FALSE) for row 2 and skip it. Then a (TRUE, TRUE) and evaluate the rest of the formula for an average. But, instead, it seems to evaluate the IF for the entire formula to a FALSE and bail out.
5.jpg
on the next step it errors and produces a #VALUE for the entire formula.
\1
I either dont understand something here or, I am doing something wrong...or both.
Any input would be greatly appreciated.
Thanks,
josh
Bookmarks