# Help Sorting Product Breakdown Structure for Minimum, MostLikely & Maximum Costs if multiple conditions are met

#### Iain A

##### New Member
Struggling need help! Copying values below a value if certain conditions are met, then copying values below if another condition is met and so on

#### Attachments

• 15.1 KB Views: 8

#### vletm

##### Excel Ninja
Iain A
Are You shown results correct in Summary-sheet?
Did You explain somewhere ... if certain conditions are met ... if another condition is met ... ?
All other than MostLikely (Mode) values could get like this.

#### Attachments

• 20.5 KB Views: 3

#### Mohammed Akram

##### New Member
Struggling need help! Copying values below a value if certain conditions are met, then copying values below if another condition is met and so on

#### Peter Bartholomew

##### Well-Known Member
The worst challenge was assigning the Max/ML/Min assumptions to data since these only appeared as level headings rather than as a field describing each record of the table. As a result I used 3 named ranges Max/ML/Min to identify the rows corresponding to the particular assumption. For any given calculation, I intersected the field to be aggregated with the assumption to narrow down the ranges appearing in the AVERAGEIFS (typically the AVERAGEIFS then acted to return a single value). The goal was to create a formula that could be propagated through an entire summary range using Ctrl+Enter. The columns could have been dynamic spilt arrays but I chose not to do that and, instead, made 'part' a relative reference.

#### Attachments

• 19.1 KB Views: 7

#### Peter Bartholomew

##### Well-Known Member
By way of explanation: the named formula 'assumption'
= IF( Case, CHOOSE( Case, Min, ML, Max ), tblPBS )
uses values written above each column, 'Case', to determine which region of the data table to select.
Case=0 returns the entire table.
= AVERAGEIFS( assumption tblPBS[Dev], .....................….....'Intersects the assumption and the development cost column'
...........................assumption tblPBS[Name], Part, .................'match the Name column to the Part in column A'
.........…………….assumption tblPBS[Cost Type], "System"), 'return only rows for which the cost type is system'
…. "" ) ........................................................................................'return blank if no part name is specified'

Last edited:

#### Iain A

##### New Member
By way of explanation: the named formula 'assumption'
= IF( Case, CHOOSE( Case, Min, ML, Max ), tblPBS )
uses values written above each column, 'Case', to determine which region of the data table to select.
Case=0 returns the entire table.
= AVERAGEIFS( assumption tblPBS[Dev], .....................….....'Intersects the assumption and the development cost column'
...........................assumption tblPBS[Name], Part, .................'match the Name column to the Part in column A'
.........…………….assumption tblPBS[Cost Type], "System"), 'return only rows for which the cost type is system'
…. "" ) ........................................................................................'return blank if no part name is specified'
Thanks Peter you have been a great help

#### Iain A

##### New Member
Iain A
Are You shown results correct in Summary-sheet?
Did You explain somewhere ... if certain conditions are met ... if another condition is met ... ?
All other than MostLikely (Mode) values could get like this.