Forum Discussion
MAX or LARGE functions not working with derived values
I suspect that the values in Q9:Q17 are text values, even though they look like numbers.
Try the following:
- Select Q9:Q17.
- Set the number format to General.
- On the Data tab of the ribbon, click Text to Columns.
- Select Delimited, then click Finish.
Does that make a difference?
HansVogelaarI 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.
=IF(AND(AND(Q5>0,Q$4<>"N",Q$4<>"O")),"100","99")
returns a text value because of the quotes around 100 and 99. Change the formula to
=IF(AND(AND(Q5>0,Q$4<>"N",Q$4<>"O")),100,99)
