The MAX and LARGE functions ignore (?) values in a range if those values are derived from an =cell operation.
In my worksheet, the values for cells P19 - P27 are generated by using =Q9 through =Q17 operations.
Once these values are in the P19 through P27 cells they are not considered by the MAX or LARGE functions when using them in a range to be evaluated. As shown in the image, each of the MAX functions is evaluating six cells, and even though all three values in the "P" column are larger than the largest value in the "Q" column, it's the largest "Q" column value reported.
I've tried putting the MAX and LARGE formulas below and to the right of the evaluated ranges and that does not help. I also turned on the Enable Iterative Calculation option in the Options / Formulas area. Is there any way to get these functions to work with derived values?
@Hans VogelaarI tried that but it didn't work. I then simplified my setup by revising the the entries in the first table, which allowed me to do away with the second table. The formulas in the Q9-Q17 cells look like this: =IF(AND(AND(Q5>0,Q$4<>"N",Q$4<>"O")),"100","99"), so they will produce either 100, 200, 300, or 99 depending on the conditions.
I then added the =MAX(Q9:Q11) formula in the "V9" cell. As the =MAX(Q9:Q11) didn't work in the V9 cell, I then inserted the "R" column and added the 1, 2, 3 values so that I would have an array. In the V12 and V15 cells I set the formula(s) to be =MAX(Q12:R14) and =MAX(Q15:R17). So, if I use the =MAX formula without an array, I get (0), If I use it with an array, I get the largest number in the "R" column. Again, if I put the =MAX formulas down below the table (say in cells C18-V20), I still get the same results.
