Forum Discussion
How can I calculated the maximum of the values in a column based on conditions on other columns
This is an Excel 2010 problem. I know that in later Excel versions there is a MAXWENN function and a MAXWENNS function but I cannot use those because in this company there is only Excel 2010. I h...
Hi Sergei,
I tried that too.. but it did not change the result.
I tried that too.. but it did not change the result.
Stefan,
I'm in English interface, bit hard to sort out your formula. Instaed i reproduce small table, hope close to your logic
Fomula is (array one)
=MAX(
IF(
([@Nr]=Table1[[#All],[Ref]])*(Table1[[#All],[Status]]<>"done"),
Table1[[#All],[Res '[Tage']]]
)
)Try to open attached file, it shall be shown in your local interface.
In addition - AND doesn't work in array formulas. Instead of AND(range1, range2) it shall be
range1*range2
- Sergei,
thanks! I understand the problem with AND now. I changed the operator to *. However, if still does not work in my sheet. In my sheet, the Max Column and the Res [Tage] Column are the same column. Can this be the reason?Stefan,
Yes, in this case you shall receive circular reference error or zero if in options you allowed iterative calculations
If to simplify on one cell operation you try to take the existing cell value, compare with something and put into this cell new value depends on results - that doesn't work with formulas.