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 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
Hi Stefan,
Do you use your formula as array one (Ctrl+Shift+Enter)?
- Hi Sergei,
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.
