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.

 

[SQUIDD]

Ok, i with you on what you are trying to do, but to what end?
Are you saying your data set will change, meaning that column A may contain more than just 2 varients and in turn H:J and K:M could also have additional triple colum sets after them.

cheers

dave