• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

  • PBS Sorting(1).xlsx
    15.1 KB · Views: 8
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

  • PBS Sorting(1).xlsx
    20.5 KB · Views: 3
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

  • PBS Sorting (PB).xlsx
    19.1 KB · Views: 7
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:
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
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.
Thanks vletm for your assistance
 
Back
Top