Sep 27 2017
06:02 AM
- last edited on
Jul 25 2018
10:08 AM
by
TechCommunityAP
Sep 27 2017
06:02 AM
- last edited on
Jul 25 2018
10:08 AM
by
TechCommunityAP
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?
Sep 27 2017 06:12 AM
Hi Stefan,
Do you use your formula as array one (Ctrl+Shift+Enter)?
Sep 27 2017 08:16 AM
Sep 27 2017 09:00 AM
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.
Sep 27 2017 09:10 AM
In addition - AND doesn't work in array formulas. Instead of AND(range1, range2) it shall be
range1*range2
Sep 28 2017 12:44 AM
Sep 28 2017 02:55 AM
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.