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

Copper Contributor

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?

 

Bildschirmfoto vom 2017-09-27 145044.png

 

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

RefMax.JPG

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

Iterative.JPG

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.