Match formula when arrays are not identical

I need to add multiple conditions and looking for a formula that summarizes the result. Please see the excel spreadsheet attached.
In the table below I would like to get a summary table by applying a formula based on the several conditions.They are
IF all the three (Self, Internal and External) are present then the Formula should be(20%Self+10%Internal+30%External)
IF only External and Internal are present then the formula should be (70%external+ 30% internal)
If only External and self are present then the formula should be (70% external+30% Internal)
If only Internal and self are present then the formula should be (30% Internal+70% self)
If only one type of Nomination (Self, Internal or External) if present then. Apply 100% of that.
Employee ID      Nomination      Part 1      Part 2      Part 3      Part 4      Part 5      Part 6      Part 7
1      Self      4.0            2.0                  4.0      4.0
1      Internal      2.0                  2.0      4.0            3.0
1      External      3.0      2.0                  1.0      2.0      
      result      3.1      2.0      2.0      2.0      1.9      2.6      3.7
2      Self            2.0                  4.0      3.0      5.0
2      Internal      3.0      3.0      6.0                        2.0
2      External            5.0      2.0      2.0            2.0      
      result      0.3      4.2      3.2      2      4      2.3      4.1
                                                
Employee ID      Summary                                          
1            3.1      2      2      2      1.9      2.6      3.7
2            0.3      4.2      3.2      2      4      2.3      4.1
Katherine82Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Katherine82Author Commented:
In the table below I would like to get a summary table by applying a formula based on the several conditions.They are
IF all the three (Self, Internal and External) are present then the Formula should be(20%Self+10%Internal+30%External)
IF only External and Internal are present then the formula should be (70%external+ 30% internal)
If only External and self are present then the formula should be (70% external+30% Internal)
If only Internal and self are present then the formula should be (30% Internal+70% self)
If only one type of Nomination (Self, Internal or External) if present then. Apply 100% of that.
Employee ID      Nomination      Part 1      Part 2      Part 3      Part 4      Part 5      Part 6      Part 7
1      Self      4.0            2.0                  4.0      4.0
1      Internal      2.0                  2.0      4.0            3.0
1      External      3.0      2.0                  1.0      2.0      
      result      3.1      2.0      2.0      2.0      1.9      2.6      3.7
2      Self            2.0                  4.0      3.0      5.0
2      Internal      3.0      3.0      6.0                        2.0
2      External            5.0      2.0      2.0            2.0      
      result      0.3      4.2      3.2      2      4      2.3      4.1
                                                
Employee ID      Summary                                          
1            3.1      2      2      2      1.9      2.6      3.7
2            0.3      4.2      3.2      2      4      2.3      4.1
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
there's no file attached...
byundtMechanical EngineerCommented:
This problem would have been a lot easier to solve had a sample workbook been posted. I tried to reconstruct your data from the Question, but did not succeed fully.

That said, I believe that you can build a truth table with the percentages for the seven different combinations of whether values are present or not. You can then use a SUMPRODUCT formula like:
=SUMPRODUCT(INDEX($B$17:$H$19,,(C2<>"")+2*(C3<>"")+4*(C4<>"")),C2:C4)

In the above formula, the truth table is in B17:H19 and C2:C4 are the raw scores. The approach used is to treat the presence or absence of Self, Internal and External scores as binary digits. So Self would be a 1 if present, Internal would be a 2 and External would be a 4. Putting those 3 binary digits together results in a number between 1 and 7. The INDEX function uses that number to pick a column of percentages from the truth table. The SUMPRODUCT function then multiplies those percentages times the raw scores.

I'm attaching my test file so you can try different input combinations and verify that the formula gives the desired results.
3FactorEvaluationQ28737993.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.