 Highlighted
New Contributor

# 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 have a small table of tasks in different hierarchical levels (objective, milestone, task, subtask). Now I estimate for all Tasks of any level that have no children how many more days it will take me to complete the task (Restdauer [Tage]). For every task that has children I want to estimate the completion time as the max over the completion times of all children by the following formula:

=MAX(WENN(UND([@[Lfd. Nr.]]=Liste1_1[[#Alle];[Referenz]];NICHT(Liste1_1[[#Alle];[Status]]="done"));Liste1_1[[#Alle];[Restdauer '[Tage']]]))

Referenz contains the reference to the ID of the mother task and Lfd. Nr. is the ID of the mother task.

However I always get 0. In the example the result should be 2. How can I fix it? 6 Replies

# Re: How can I calculated the maximum of the values in a column based on conditions on other columns

Hi Stefan,

Do you use your formula as array one (Ctrl+Shift+Enter)?

Highlighted

# Re: How can I calculated the maximum of the values in a column based on conditions on other columns

Hi Sergei,

I tried that too.. but it did not change the result.
Highlighted

# Re: How can I calculated the maximum of the values in a column based on conditions on other columns

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.

Highlighted

# Re: How can I calculated the maximum of the values in a column based on conditions on other columns

In addition - AND doesn't work in array formulas. Instead of AND(range1, range2) it shall be

range1*range2

Highlighted

# Re: How can I calculated the maximum of the values in a column based on conditions on other columns

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?
Highlighted

# Re: How can I calculated the maximum of the values in a column based on conditions on other columns

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.