Weighted Average / IF statement

[excelstuckinthemud1]

[FONT=&quot]Hi all,

[/FONT]
[FONT=&quot]Any help would be appreciated.[/FONT]
[FONT=&quot]
I am trying to create a way to model some data in Excel. This involves taking three sections (lets call them a,b,c) and their sub-sections (lets call them A1, A2, A3, A4, B1, B2, B3, B4, B5, C1, C2, C3) and adds individual confidence ratings to these sub sections then creates an overall confidence rating based on the individual answers. These confidence ratings are Very Low, Low, Medium, High, Very High.

I have done a weighted average for each of the sections (which works), however I am trying to find a way to add specific rules (that overwrite the weighted average results if specific inputs are selected), such as:
[/FONT]

• If Very Low is put for any A OR any B, then overall confidence will be VL regardless of weighted average.
• If Low is put for any 2As OR 2Bs, then overall will be L regardless of weighted average.

[FONT=&quot]Anyone have an idea of how to model the additional requirements over the weighted average?[/FONT]

 

[Special-K99]

So I'm assuming the following as we have no idea what your spreadsheet looks like.

Column A is either a b or c
Column B is one of your sub sections (again another assumption since you haven't specified this, sub sections A1 A2 A3 A4 A5 - they can only apply to section a presumably? And any subsections beginning B can only apply to section b, and C sub sections can only apply to section c?)
Column C overall confidence ratings

For the VL rule
=IF(SUMPRODUCT((LEFT(B1:B1000,1)={"A","B"})*(C1:C1000="VL")),"VL","Weighted average") <== insert your weighted average value here

The L rule:
Do you mean AT LEAST 2As or 2Bs or EXACTLY 2As or 2Bs, you havent specified.
At the moment your descriptions suggests there must be EXACTLY 2As or 2Bs (which I doubt)