Formula to quantify the weight of each part of a whole?

[Katie620]

This is borderline just a math question but here goes

Lets say we have an estimated number of days something takes, the actual number of days it ended up taking, and a % that when you divide the two it gives you an idea of the performance.

Lets say there are several of these that add up to a total % which gives the performance of the total factory or whatever:

So:

NAME	Completion %	Estimated Days	Days it actually took
A	100%	5	5
B	50%	2	4
C	53%	34	64
D	71%	17	24
E	46%	3	6.5
F	72%	13	18
TOTAL	61%	74	121.5

So you can see in order to see who really drove the total 61% you have to try to look at who had the most days and try to guess at it.

My question: is there some kind of weighted average formula or some other method that will allow me to more easily quantify the major drivers of the low 61% total?

Thanks!

[MrShorty]

You're right that is probably more math than Excel.

I'm sure there are many different ways to analyze this, and I don't know what is The Best (TM) way.

One might look at how many days each contributes to the totals =C2/C$8 copied down and across. These numbers show that C contributes about half of the total (estimated and actual). If you add a difference column (D2-C2), then one can see that C contributes 30/44.5 to the total extra days.

I would expect that, any way you try to slice this data, C is going to be the main driver behind "low" or "high" results. This is because, from the initial estimates, C is the main contributor to the project.

[Glenn Kennedy]

Yep. Agreed. It has to be the largest individual contribution to the over-run. Maybe like this (array formulae):

=MAX($D$4:$D$9-$C$4:$C$9)

=INDEX($A$4:$A$9,MATCH(MAX($D$4:$D$9-$C$4:$C$9),$D$4:$D$9-$C$4:$C$9,0))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[Katie620]

Thank you both so much!!

Not sure how to make it draggable, but just to make sure, can I alter your MAX array formula like so, in order to see the 2nd+ largest contributors?



=INDEX($A$4:$A$9,MATCH(LARGE($D$4:$D$9-$C$4:$C$9,2),$D$4:$D$9-$C$4:$C$9,0))

[Glenn Kennedy]

Yep. I'd set it up like this, probably. anyhow..

[Glenn Kennedy]

No I wouldn't... I'd set it up like this, to deal with tied results.

[Katie620]

Thanks again!! Wow ok so this has the potential to be way more complicated than I realized.

You've given me plenty to go on, so if you want to stop here I understand and thanks again

If not, I've attached a workbook with more accurate data :P

First, assuming I set it up right it looks like your formulas aren't working correctly if there are several tied results. (I highlighted the rows that look weird).

I added a section to the right where I just reversed the subtraction arrays in order to try to rank those that are above 100%.

Ideally:

• In the lower than 100% contributors column nothing would show up for those that are at 100% or higher. I tried this with an if statement but there is something weird going on with your contribution column.
• For those that are greater than 100% column, nothing would show up for those that are less than 100% and ideally those that are tied at 100% will be ranked by the size of their days (larger 100% days first)
• Any ties are ranked by size

I know I started with a simple question and am now asking for more complicated things Any suggestions appreciated, but I have a lot to go on already thanks again!

[Glenn Kennedy]

Reply will follow in bite-sized chunks. IMHO, the biggest driver to an failure to meet targets is the MAGNITUDE of the difference between C & D. Of the 3 @ 50%, Person 10 took 10 days more than anticipated, whereas Persons 9 & 22 each took only 5 days more. To my (twisted??) logic, Person 10 ranks HIGHER as a driver than 9 & 22 who tie, but as lower drivers. Person 21 contributed a differenc eof only 3 days, so ranks lower. So.... I think the formula is correct.

Comments?? I'll look at the bit on the right shortly...

[Katie620]

Ah, you're absolutely right the magnitude should be the driver, I wasn't looking at it correctly.


Do you happen to know what it is about the formatting in column N that makes the IF statement not work for column O? Oh maybe it's that weird Excel floating point problem?

Yes, if you could look over the part on the right that would be great!

Thanks again!

[Glenn Kennedy]

try this...

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[Katie620]

Perfect, thank you so much!!!!