Build a formula to calculate total duration in Excel when have different parameters

0
votes

I have an Excel table with durations of individuals and I want to calculate the total duration for each activity(in this case, for each row). Each task the individuals are performing is either a parallel one or a sequential one. And it is marked with each activity. (P for Parallel and S for Sequential). There will be a drop down menu to select whether a task is parallel or sequential.

Example table is shown below.

Image

I want to calculate the total duration for each row. The conditions are as follows.

  • If all the tasks are parallel then the total duration is the maximum duration of all the tasks. (Row 1 as an example, the total duration will be 9,2 days)

  • If all the tasks are sequential then the total duration is the addition of all the individual durations. (Row 5 as an example, the total duration will be 31,2 days)

  • If there are tasks which are mixed (both parallel and sequence)

  • The maximum duration of parallel tasks must be calculated. (MAX of the parallel tasks)
  • Total of the sequential tasks must be calculated. (SUM of the sequential tasks)
  • Then add the MAX of the parallel tasks and SUM of the sequential tasks. (Row 2 as an example, the MAX of parallel tasks is 6,5 and the SUM of the sequential tasks is 16, so the total duration will be 16+6,5=22,5)

I need to build a formula in Excel 2013 for this. I tried using IF, MAX and SUM formulas, but couldn’t find the correct solution.

I want to build the formula for this. Any idea?

1
excelexcel-formulams-officeexcel-2013
Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. – halfer
Thank you @halfer for the feedback. I will take this into consideration in the future as this is my first time in Stack Overflow. – Danny
OK. Note that it is not a rule specific to Stack Overflow, and I am constantly surprised how often people think it is an appropriate way to address people working for free. We get 10 to 20 instances of hurrying and pleading a day (conservative estimate). I have a feeling it is a culturally specific phenomenon, perhaps in Indian English, but my genuinely curious enquires on that theme have not borne any fruit. – halfer

1 Answers

1
votes

Use offset ranges that are the same size.

Use Aggregate for the P and SUMIF() for the S:

=IFERROR(AGGREGATE(14,6,A2:K2/(B2:L2="P"),1),0)+SUMIF(B2:L2,"S",A2:K2)

Most likely your local setting will require ; in place of the , that my local settings require:

=IFERROR(AGGREGATE(14;6;A2:K2/(B2:L2="P");1),0)+SUMIF(B2:L2;"S";A2:K2)

enter image description here