Hi Excel Forum Team,
Please see the excel example attachment/link below for help that I need with a formula that calculates the percentage split in the Service Order Single or Split list (column D) between two sets of criteria, the first being the WBS Element list (column B) and the second being the Service Order Number list (column C). Then based upon the Service Order Contract Value list (column E), the needed formula contained in the Service Order Single or Split list (column D) will automatically show the percent split in relation the Service Order Contract Value list subtotals.
The defining difference between weather a service order is counted as either a single (100%) service order or if it is a split service order (<100%) is directed by the WBS Element list numbers.
If in each row the WBS Element list (cells B3:B13) have similar numbers (for example: 00542-100) with either similar or dissimilar numbers in the Service Order Number list (cells C3:C13), then the Service Order Single or Split list formulas (cells D3:D13) will result in 100%. In this case, with similar WBS Element numbers, it really does not matter what’s in the Service Order Number list cells.
However, If in each row the WBS Element list cells have dissimilar numbers (for example: 00542-200 and 00542-900) with the Service Order Number list cells have similar numbers (for example: B123 and B123), then the Service Order Single or Split list cell formulas will result in fragmenting the percentage (for example: 00542-200 & B123 = 60% and 00542-900 & B123 = 40%) of the total 100%. This percent split is based off of the Service Order Contract Value list (cells E3:E13) where the division of the total service order sum is split accordingly. In this case, the combination of dissimilar WBS Element numbers with similar Service Order Numbers triggers the split to occur.
Please feel free to modify or add to the original excel example attachment/link in order to help make the requested formula(s) work.
Thank you for all your help!
Calculate Percent Split of Total Value with Two Criteria.xlsx
Garrett
Bookmarks