Formula to evaluate average scenario score based on 4 people and 6 bundle submissions

[ArnoSchus]

Hello Everyone,

I am trying to find a formula allowing me to automatically have the average score of all possible scenarios based on 6 bundles quoted by 4 people.

I can allow 2 or 3 people scenarios. I can't give all the lots to 1 or 4 people. Only 2 or 3 people scenarios allowed.

Please see attached file for clear explanations.

I am quite lost to find the relevant formula. I am willing to give a go to identify all the possible scenario permutations. But would love to get your help on how to evaluate the average score ...

thanks!

AS

[Bo_Ry]

Please try at N6 Press Ctrl+Shift+Enter and drag down

=IFERROR(SUM(IFERROR(INDEX($B$6:$E$11,N(IF(1,{1,2,3,4,5,6})),N(IF(1,MATCH(H6:M6,$B$5:$E$5,)))),))/COUNTA(H6:M6),"")

[ArnoSchus]

Bo_Ry, thank you for your post. I did as explained (inclusive of CTRL / Shift / Enter) but get 0.0% as an outcome.

[Bo_Ry]

Please check attached file from Post#2

[ArnoSchus]

It does work in your file, not sure to get why not in my own.

At any rate, my deepest thanks to you Bo_Ry

Cheers
AS

[ArnoSchus]

Bo_Ry, if i may use your talents a little bit more, do you have the formulate now to evaluate the Total Cost for each scenario. Not the weighted cost, the total cost by adding the various bundle costs.

I am trying to attached the file but maybe i can't in an existing post.

Thanks

[Bo_Ry]

Please try at O6 Press Ctrl+Shift+Enter and drag down
=SUM(IFERROR(INDEX($B$6:$E$11,N(IF(1,{1,2,3,4,5,6})),N(IF(1,MATCH(H6:M6,$B$5:$E$5,)))),))


Check https://support.office.com/en-us/art...f-48361075954d on how to do Ctrl+Shift+Enter

[ArnoSchus]

Thanks Bo_Ry, it does work perfectly !

Cheers
AS