Discussion How can I calculated the maximum of the values in a column based on conditions on other columns in Excel
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/110970#M11275
<P>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.</P><P> </P><P>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:</P><P> </P><P>=MAX(WENN(UND([@[Lfd. Nr.]]=Liste1_1[[#Alle];[Referenz]];NICHT(Liste1_1[[#Alle];[Status]]="done"));Liste1_1[[#Alle];[Restdauer '[Tage']]]))</P><P> </P><P>Referenz contains the reference to the ID of the mother task and Lfd. Nr. is the ID of the mother task.</P><P> </P><P>However I always get 0. In the example the result should be 2. How can I fix it?</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Bildschirmfoto vom 2017-09-27 145044.png" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/21068i715473673EE338A3/image-size/large?v=1.0&px=999" role="button" title="Bildschirmfoto vom 2017-09-27 145044.png" alt="Bildschirmfoto vom 2017-09-27 145044.png" /></span></P><P> </P>Wed, 25 Jul 2018 17:08:56 GMTStefan Meier2018-07-25T17:08:56ZHow can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/110970#M11275
<P>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.</P><P> </P><P>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:</P><P> </P><P>=MAX(WENN(UND([@[Lfd. Nr.]]=Liste1_1[[#Alle];[Referenz]];NICHT(Liste1_1[[#Alle];[Status]]="done"));Liste1_1[[#Alle];[Restdauer '[Tage']]]))</P><P> </P><P>Referenz contains the reference to the ID of the mother task and Lfd. Nr. is the ID of the mother task.</P><P> </P><P>However I always get 0. In the example the result should be 2. How can I fix it?</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Bildschirmfoto vom 2017-09-27 145044.png" style="width: 999px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/21068i715473673EE338A3/image-size/large?v=1.0&px=999" role="button" title="Bildschirmfoto vom 2017-09-27 145044.png" alt="Bildschirmfoto vom 2017-09-27 145044.png" /></span></P><P> </P>Wed, 25 Jul 2018 17:08:56 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/110970#M11275Stefan Meier2018-07-25T17:08:56ZRe: How can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/110984#M11276
<P>Hi Stefan,</P><P> </P><P>Do you use your formula as array one (Ctrl+Shift+Enter)?</P>Wed, 27 Sep 2017 13:12:44 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/110984#M11276Sergei Baklan2017-09-27T13:12:44ZRe: How can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111101#M11277
Hi Sergei,<BR /><BR />I tried that too.. but it did not change the result.Wed, 27 Sep 2017 15:16:32 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111101#M11277Stefan Meier2017-09-27T15:16:32ZRe: How can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111145#M11278
<P>Stefan,</P><P> </P><P>I'm in English interface, bit hard to sort out your formula. Instaed i reproduce small table, hope close to your logic</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="RefMax.JPG" style="width: 524px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/21128i7F928A4E6BA596ED/image-size/large?v=1.0&px=999" role="button" title="RefMax.JPG" alt="RefMax.JPG" /></span></P><P>Fomula is (array one)</P><PRE>=MAX(
IF(
([@Nr]=Table1[[#All],[Ref]])*(Table1[[#All],[Status]]<>"done"),
Table1[[#All],[Res '[Tage']]]
)
)</PRE><P>Try to open attached file, it shall be shown in your local interface.</P><P> </P>Wed, 27 Sep 2017 16:00:17 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111145#M11278Sergei Baklan2017-09-27T16:00:17ZRe: How can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111147#M11279
<P>In addition - AND doesn't work in array formulas. Instead of AND(range1, range2) it shall be</P><P>range1*range2</P>Wed, 27 Sep 2017 16:10:07 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111147#M11279Sergei Baklan2017-09-27T16:10:07ZRe: How can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111409#M11280
Sergei,<BR /><BR />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?Thu, 28 Sep 2017 07:44:20 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111409#M11280Stefan Meier2017-09-28T07:44:20ZRe: How can I calculated the maximum of the values in a column based on conditions on other columns
https://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111441#M11281
<P>Stefan,</P><P> </P><P>Yes, in this case you shall receive circular reference error or zero if in options you allowed iterative calculations</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Iterative.JPG" style="width: 775px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/21212iA0FF1BF1480CF1E9/image-size/large?v=1.0&px=999" role="button" title="Iterative.JPG" alt="Iterative.JPG" /></span></P><P>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.</P><P> </P>Thu, 28 Sep 2017 09:55:36 GMThttps://techcommunity.microsoft.com/t5/excel/how-can-i-calculated-the-maximum-of-the-values-in-a-column-based/m-p/111441#M11281Sergei Baklan2017-09-28T09:55:36Z