Link to home
Create AccountLog in
Avatar of Katherine82
Katherine82

asked on

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
Avatar of Katherine82
Katherine82

ASKER

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
Avatar of Ryan Chong
there's no file attached...
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.