I have a variable-length data table that I format, sort, and sub-total with VB. I almost have this to completion, but I can't figure out the final VB steps and have been resorting to manual entry thus-far.
Within each separate sub-total group I need to caculate a weighted average using each calcluated sub-total. Each sub-total will vary in length, but the sub-total names remain constant day-to-day.
The data always starts in A4 with these column headings:
Skillset (name), Group (name), CallsAnswered (count), AvgAnsweredDelay (duration), and WeightedAvgAnsweredDelay. Each Skillset fits in a Group and the Group column is the sub-total by. WeightedAvgAnsweredDelay is a sub-totaled column totaling to zero and is where I need to input the formula: =(C5/$C$9)*D5 or =(Count/TotalCount)*Duration. The next line (E6) would be =(C6/$C$9)*D6 progressing on until the sub-total for that Group is reached and then re-starting at the next sub-total Group.
The $C$9 could vary from day-to-day as a Skillset could be absent or more could be added. These variations then "roll-down" throughout each sub-total changing the starting and finishing point of each.
I've thought about other options, but I need to account for new Skillsets being added without my knowledge which prevents the use of a template Skillsets list. I've spent days on this specific issue with little progress and I'm hoping there is something easy that I'm missing. This forum has been instrumental in getting me this far, along with a Bill "MrExcel" Jelen book. I'm looking to put this issue to rest and move on to the next challenge. Thanks!
Within each separate sub-total group I need to caculate a weighted average using each calcluated sub-total. Each sub-total will vary in length, but the sub-total names remain constant day-to-day.
The data always starts in A4 with these column headings:
Skillset (name), Group (name), CallsAnswered (count), AvgAnsweredDelay (duration), and WeightedAvgAnsweredDelay. Each Skillset fits in a Group and the Group column is the sub-total by. WeightedAvgAnsweredDelay is a sub-totaled column totaling to zero and is where I need to input the formula: =(C5/$C$9)*D5 or =(Count/TotalCount)*Duration. The next line (E6) would be =(C6/$C$9)*D6 progressing on until the sub-total for that Group is reached and then re-starting at the next sub-total Group.
The $C$9 could vary from day-to-day as a Skillset could be absent or more could be added. These variations then "roll-down" throughout each sub-total changing the starting and finishing point of each.
I've thought about other options, but I need to account for new Skillsets being added without my knowledge which prevents the use of a template Skillsets list. I've spent days on this specific issue with little progress and I'm hoping there is something easy that I'm missing. This forum has been instrumental in getting me this far, along with a Bill "MrExcel" Jelen book. I'm looking to put this issue to rest and move on to the next challenge. Thanks!