Need your help understanding how excel iterates through data sets in formulas

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

I cannot help you with how Excel thinks, not being a programmer. However, I do know that when working with Array formulas, Excel doesn't like AND statements. I would exchange it for a nested IF. I'm not sure if I have all the parentheses in the right place but something like this

=AVERAGE(IF(Categories1[Column1]="T&M",IF(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),"")))

Can you briefly describe what you're trying to average? Not in formula-speak, just in English...

Column C is the contract?

So, for each row that has T&M in column C, sum give the average of value from B/D, not including rows with an error in column B?

Does this array give you what you want?

=AVERAGE(IF(NOT(ISERROR(Categories1[Check]))+(Categories1[Column1]="T&M")=2, Categories1[Check]/Categories1[Rate/day]))