Dividing values across cells based on states

[caatodd]

Hi everyone,

I'm trying to calculate how much resource is required across multiple teams based on billed hours.

For this example I have 3 teams that work on 3 clients. I'm using estimates to determine what hours are required for each client/team. So Team A would typically action 44% of the hours, Team B 25% and Team C 31%.

However some clients might not need every team, Team C might not actually work on client C. Those hours would then need to be allocated to the other teams, as the client is still paying for those hours so they need actioning. I've got a column to basically say whether a team is required and have "yes" or "no" states. The idea would be that if a team has a "no" state, then it's hours are split across the other team. The tricky bit is I would need those hours split based on the ratios I have above, so Team A would get more of those hours than Team C as Team A.

I've tried to use IF statements to get this working, by multiplying Team C's percentage by Team A's percentage to get Team A's share of the hours, which are then added to Team A's hours.
=IF(C2="no",0,(IF(C2="yes",0.44,0%)+IF(F2="no",0.44*0.25,0))+IF(I2="no",0.44*0.31,0))

The issue I've run into is when two teams (Team B and C) are not required the hours are still being shared across two teams (Team A and Team B) rather than the 1 remaining team with "Yes" (Team A) whereas I want the other team to affectively have 100% of the hours, however what I have currently is not doing this. I could get this working using a long list of IF statements but I can't imagine this is the most efficient way of doing it and doesn't upscale well if I want to add more teams in the future.

Is there a better way to do this?

Thanks

Chris

[MrShorty]

It's not clear to me exactly how you want to redistribute things (maybe because it is not clear to you??). If you are uncertain how to do this, my thought was to "normalize" the percentages for each scenario and use the normalized fractions to determine the hours. Here's how I did it:

1) I see that the base/reference case is team A 44%, team B 25%, team C 31%. Part of the problem that I see is trying to do everything in single cells, so my first change is to move the base case reference fractions into helper cells. I enter 0.44 into N1, 0.25 into O1, and 0.31 into P1.
2) My normalization formula is value/sum(values). If I can get my Yes/No text values converted to a 1x3 block of 1s and 0s, then my numerator becomes a simple product function and my denominator is a simple SUMPRODUCT(). In N2, I enter =IF(C2="yes",1,0), In O2 =IF(F2="Yes",1,0), In P2 =IF(I2="Yes",1,0).
3) Now my normalization function is fairly simple. In Q2, I enter =N2*N$1/SUMPRODUCT($N$1:$P$1,$N2:$P2). Note the mix of relative and absolute references for easy copying. Then copy into Q2:S4. Column D then becomes a copy of column Q and so on for the other % columns. The hours columns are unchanged.
4) If I find these helper columns offensive to look at, then I can hide them.

If you don't have a specific algorithm in mind, that seems to meet the relatively vague criteria you described. What do you think?

[caatodd]

MrShorty my friend, that is absolutely perfect!!

Your assumptions were correct. I wanted to split the remaining hours across the other teams according to the fraction of time usually required for each team. That's exactly what you've done.

Apologies for the delay in coming back to you. I had to drop this project for another but back to this one and you've nailed it. Huge thanks!