Match formula when arrays are not identical

Katherine82
Katherine82 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

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 ChongSoftware Team Lead

Commented:
there's no file attached...
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial