Excel Experts - Best approach to calculate monthly burn rate

[ideasfactory]

Hi,

I need some guidance from you excel guru's out there. I have attached am example file for you review.

In summary I need a formula (not VBA) in Column C to calculate team monthly burn rate from Column G, based on the combination of Delivery Team row from Column A and team size chosen in the same row in column B (which is a list lookup from Column O)

See the 'Formula Scenarios' for the specific requirements

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Delivery Team	Team Size	Monthly Team Burn $		Resource	Hour Rate	Monthly Burn Rate $	Proj Man & Analyst - Small Team	Proj Man & Analyst -Medium Team	Proj Man & Analyst -Large Team	Dev Team - Small	Dev Team - Medium	Dev Team - Large		Lookup Team Size - Validation List
2	Project Man & Analysts Team	Small	Calc ?		Project Manager	$105	$15,225		Y	Y					Small
3	Dev Team	Medium	Calc ?		Business Analyst - Senior	$95	$13,775	Y	Y	Y					Medium
4					Business Analyst	$80	$11,600	Y		Y					Large
5	Total		Sum of above		Change Manager	$125	$18,125		Y	Y
6					Developer - Senior	$85	$12,325					Y	Y
7					Developer	$90	$13,050				Y		Y
8					Solution Analyst	$91	$13,147				Y	Y	Y
9
10
11	Formula Scenarios:	If user selects 'Small' in cell B2 then cell C2 should lookup Column H where rows have 'Y' and SUM Monthly Burn Rate from Column G
12		If user selects 'Medium' in cell B2 then cell C2 should lookup Column I where rows have 'Y' and SUM Monthly Burn Rate from Column G
13		If user selects 'Large' in cell B2 then cell C2 should lookup Column J where rows have 'Y' and SUM Monthly Burn Rate from Column G
14
15		If user selects 'Small' in cell B3 then cell C3 should lookup Column K where rows have 'Y' and SUM Monthly Burn Rate from Column G
16		If user selects 'Medium' in cell B3 then cell C3 should lookup Column L where rows have 'Y' and SUM Monthly Burn Rate from Column G
17		If user selects 'Large' in cell B3 then cell C3 should lookup Column M where rows have 'Y' and SUM Monthly Burn Rate from Column G
18

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Link to Excel File -> Here

I am open to better ways to store the data from columns E to M but it would be good to keep this structure as it is easier to populate.

Hope someone can help.

The solution to this may be value to others.

 

[etaf]

you can use a nested IF and then use SUMIF()
Assuming the layout is not going to change and that
A2 is always Project Man & Analysts Team
A3 is always Dev Team

=IF(B2="small",SUMIF(H:H,"y",G:G),IF(B2="medium",SUMIF(I:I,"y",G:G),IF(B2="large",SUMIF(J:J,"y",G:G),"error")))

But it really depends on how flexible you want this to be

then for Dev team
just change the reference
=IF(B3="small",SUMIF(K:K,"y",G:G),IF(B3="medium",SUMIF(L:L,"y",G:G),IF(B3="large",SUMIF(M:M,"y",G:G),"error")))

you may not need the 3rd IF , asyou have data validation on for B2/B3
=IF(B2="small",SUMIF(H:H,"y",G:G),IF(B2="medium",SUMIF(I:I,"y",G:G),SUMIF(J:J,"y",G:G)))
and assume because small and medium is not selected it must be large

 

[SQUIDD]

Hello

will this formual work for you??

=IF(B2="Small",SUMIF(H:H,"Y",G:G),IF(B2="Medium",SUMIF(I:I,"Y",G:G),IF(B2="Large",SUMIF(J:J,"Y",G:G),"")))

regards

dave

 

[SQUIDD]

Hi Wayne

looks like you posted seconds before me LOL

dave

 

[etaf]

happens to me a lot also , but often see different approaches like that

 

[SQUIDD]

sometimes frustrating, but all good if OP gets what they want.

Dave

 

[ideasfactory]

Gents,

Thanks for your posts........Yes these will work but Wayne has got me thinking can this be a little more flexible not static?
I have updated below by adding a row specifying the team size in H2:M2

Is it possible for the formula in column C to:

1. Lookup the Delivery Team name set in Column A and match it against cell value in range from Column H1 to M1 then
2. Match the Team Size selection in Column B against range from Column H2 to M2 then for all values in that column range
3. Sum the monthly burn rate $ from Column G where "Y" is present

Cheers both.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Delivery Team	Team Size	Monthly Team Burn $		Resource	Hour Rate	Monthly Burn Rate $	Proj Man & Analyst	Proj Man & Analyst	Proj Man & Analyst	Dev Team	Dev Team	Dev Team		Lookup Team Size - Validation List
2								Small	Medium	Large	Small	Medium	Large
3	Project Man & Analyst	Small	$0		Project Manager	$105	$15,225		Y	Y					Small
4	Dev Team	Medium	$0		Business Analyst - Senior	$95	$13,775	Y	Y	Y					Medium
5					Business Analyst	$80	$11,600	Y		Y					Large
6	Total		$0		Change Manager	$125	$18,125		Y	Y
7					Developer - Senior	$90	$13,050					Y	Y
8					Developer	$85	$12,325				Y		Y
9					Solution Analyst	$80	$11,600				Y	Y	Y
10
11
12	Formula Scenarios:	If user selects 'Small' in cell B2 then cell C2 should lookup Column H where rows have 'Y' and SUM Monthly Burn Rate from Column G
13		If user selects 'Medium' in cell B2 then cell C2 should lookup Column I where rows have 'Y' and SUM Monthly Burn Rate from Column G
14		If user selects 'Large' in cell B2 then cell C2 should lookup Column J where rows have 'Y' and SUM Monthly Burn Rate from Column G
15
16		If user selects 'Small' in cell B3 then cell C3 should lookup Column K where rows have 'Y' and SUM Monthly Burn Rate from Column G
17		If user selects 'Medium' in cell B3 then cell C3 should lookup Column L where rows have 'Y' and SUM Monthly Burn Rate from Column G
18		If user selects 'Large' in cell B3 then cell C3 should lookup Column M where rows have 'Y' and SUM Monthly Burn Rate from Column G

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[SQUIDD]

YOU MEAN SOMETHING LIKE THIS??

=IF(A2="Project Man & Analyst",IF(B2="Small",SUMIF(K:K,"Y",G:G),IF(B2="Medium",SUMIF(L:L,"Y",G:G),IF(B2="Large",SUMIF(M:M,"Y",G:G),""))),IF(B1="Small",SUMIF(H:H,"Y",G:G),IF(B1="Medium",SUMIF(I:I,"Y",G:G),IF(B1="Large",SUMIF(J:J,"Y",G:G),""))))

Dave

 

[ideasfactory]

Hi Dave,

Thanks but I might not have explained properly and this may not be possible in formula I do not know:

As per last embedded sheet for example cell C3 function to:

1. Lookup in range H1:M1 based on value in A3 then
2. Locate the column that contains value in B3, in this case "Small" in cell H2
3. Look down column H and for those that have "Y" sum the burn rate from Column G.

I think point 2 might be tricky but there may be a combination of nested formulas to do it what do you reckon?

 

[SQUIDD]

oops

sorry, maybe not what you wanted but the above would be wrong in any case, sorry

=IF(A2="Project Man & Analyst",IF(B2="Small",SUMIF(H:H,"Y",G:G),IF(B2="Medium",SUMIF(I:I,"Y",G:G),IF(B2="Large",SUMIF(J:J,"Y",G:G),""))),IF(A2="Dev Team",IF(B2="Small",SUMIF(K:K,"Y",G:G),IF(B2="Medium",SUMIF(L:L,"Y",G:G),IF(B2="Large",SUMIF(M:M,"Y",G:G),""))),""))

try this one

JUST SEEN YOU LAST POST, SO WILL HAVE A READ.

dave