Help: Find a value combining criteria (formulas)

ctorohuamanchumo

New Member
Joined
Apr 13, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Greetings,

I am having several problems with creating a formula.
I am performing a pairwise comparison (eg. Criteria 1 vs Criteria 2, 3, 4... / Criteria 2 vs Criteria 3, 4, 5... / Criteria 3 vs Criteria 4, 5, 6, etc).

Explanation:
- Columns A and F have the weights of each criterion (naturally, there are duplicates values)
- The column "H" has the division of scores C/D

I need to create these two formulas:

1) I need to select the value in column "H" that meet two criteria: Correspond to the row of the Criteria in column "B" with the highest weight in "A" PLUS Correspond to the row of the Criteria in column "E" with the SECOND highest weight in "F".
For example, if Criterion 4 has the highest weight in column A and Criterion 8 has the SECOND highest weight in Column F, I need a formula to automatically get that 1/2 in column "H"
It is important to mention that, I want the 2nd highest value in F in the group that has the first highest value in A, not just the 2nd highest value

2. I need to select the value in column "H" that meet two criteria: Correspond to the row of the Criteria in column "B" with the SECOND highest weight in "A" PLUS Correspond to the row of the Criteria in column "E" with the THIRD highest weight in "F".
It is important to mention that, I want the 3rd highest value in F in the group that has the second highest value in A.

This task is very urgent and I would really appreciate your help!
This is very important for me, because I need to get 10 values:
1st highest (reference Column A) and 2nd highest (reference Column F)
1st highest (reference Column A) and 3rd highest (reference Column F)
1st highest (reference Column A) and 4th highest (reference Column F)
1st highest (reference Column A) and 5th highest (reference Column F)
2nd highest (reference Column A) and 3rd highest (reference Column F)
2nd highest (reference Column A) and 4th highest (reference Column F)
2nd highest (reference Column A) and 5th highest (reference Column F)
3rd highest (reference Column A) and 4th highest (reference Column F)
3rd highest (reference Column A) and 5th highest (reference Column F)
4th highest (reference Column A) and 5th highest (reference Column F)
 

Attachments

  • excel.png
    excel.png
    50.3 KB · Views: 3

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I'm not sure, but if your Criterion are in Columns B and E, you might want to Translate those in English for us who don't know what it says ( I, for one, don't even know what Language it is ).

And also, providing a sample with XL2BB will be a good idea ( check my signature for where and how ).
 

ctorohuamanchumo

New Member
Joined
Apr 13, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I'm not sure, but if your Criterion are in Columns B and E, you might want to Translate those in English for us who don't know what it says ( I, for one, don't even know what Language it is ).

And also, providing a sample with XL2BB will be a good idea ( check my signature for where and how ).
Dear jtakw,

Thank you very much. I am having some troubles with XL2BB (I am at work and there are some internet restrictions).
However, I am attaching an updated image.

I need a formula that could find the value in column "H" (goes from H3 to H83) that meet these criteria:
Correspond to the row of the Criteria in column "B" with the highest weight overall in "A" PLUS Correspond to the row of the Criteria in column "E" with the SECOND highest weight in "F". It is important to mention that I want the 2nd highest value in F in the group that has the first highest value in A, not just the 2nd highest value overall.
For example, if Criterion 1 has the highest value, I would search for the second-highest value among those rows for Criteria 1. In these case, it would be row 9 (Criterion 8). Hence, the formula should give me the value 1/3.

Similarly, I need a formula that could find the value in column "H" that meet these criteria:
Correspond to the row of the Criteria in column "B" with the SECOND highest weight overall in "A" PLUS Correspond to the row of the Criteria in column "E" with the THIRD highest weight in "F".
It is important to mention that, I want the 3rd highest value in F in the group that has the second highest value in A, not just the 3rd highest value overall.
For example, if Criterion 2 has the second-highest value overall, I would search for the third-highest value among those rows for Criteria 2. In these case, it would be row 14 (Criterion 4). Hence, the formula should give me the value 1/7.

I believe that if I have the formulas for these, I could perform the remaining ones...

Thanks in advance,

Carlos J. Toro-Huamanchumo, MD

1618409046160.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find a value combining two criteria (formulas)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

ctorohuamanchumo

New Member
Joined
Apr 13, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Find a value combining two criteria (formulas)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Thanks for the advice.
Similar post in this link: Find a value combining two criteria (formulas)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,728
Members
417,108
Latest member
Thein Than

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top