Finding bottom minimum values

bbran19

New Member
Joined
Jan 30, 2012
Messages
49
Office Version
  1. 365
Platform
  1. Windows
I use this formula to assign A,B,C,D to the highest value, next highest and so on in Column H based on a list as attached using column C and D as differentiators.

=IF($H2=MAX(($C$2:$C$299=$C2)*($D$2:$D$299=$D2)*($H$2:$H$299)),"A",IF(H2=LARGE(($D$2:$D$299=$D2)*($H$2:$H$299)*($C$2:$C$299=$C2),2),"B",IF(H2=LARGE(($D$2:$D$299=$D2)*($H$2:$H$299)*($C$2:$C$299=$C2),3),"C",IF(H2=LARGE(($D$2:$D$299=$D2)*($H$2:$H$299)*($C$2:$C$299=$C2),4),"D",""))))

But I can not for the life of me workout how to assign A,B,C,D to find the Lowest value, next lowest, and so on in another column.
 

Attachments

  • Screenshot 2021-05-20 135707.png
    Screenshot 2021-05-20 135707.png
    53.5 KB · Views: 8

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
1. Please Update your Acount Details and Save it to We know what version of Excel & OS you used to we help based on.
2. I see you can Find Max And Min Based Only Column C.
AND
Please Upload your Example file with XL2BB Addin (Preferable) or Upload at Free hosting Site e.g. GoogleDrive, OneDrive or www.dropbox.com & Insert Link Here.
 
Upvote 0
if you want Use your formula for Lowest and so on, change all Large within Formula to Small.
This is Another Method Only Based Column C. Press CTRL+SHIFT+ENTER
Book1.xlsx
ABCDEFGHIJ
1RaceValueMaxMin
2573  
3591  
4552 C
5547 A
6555 D
75100C 
8550 B
95117A 
105106B 
11574  
12555 D
13576  
14596D 
15655 C
166120B 
17691  
186119C 
196122A 
20653 B
21660  
22694  
23683  
24656 D
256115D 
26686  
27640 A
28660  
29783  
30778  
31789  
32778  
33760 B
34791  
357111B 
36763 C
37777  
38742 A
39770 D
40779  
417107C 
42799D 
437117A 
44797  
45
Parameters
Cell Formulas
RangeFormula
H2:H44H2=IF(G2=LARGE(IF($C$2:$C$44=C2,$G$2:$G$44),1),"A",IF(G2=LARGE(IF($C$2:$C$44=C2,$G$2:$G$44),2),"B",IF(G2=LARGE(IF($C$2:$C$44=C2,$G$2:$G$44),3),"C",IF(G2=LARGE(IF($C$2:$C$44=C2,$G$2:$G$44),4),"D",""))))
I2:I44I2=IF(G2=SMALL(IF($C$2:$C$44=C2,$G$2:$G$44),1),"A",IF(G2=SMALL(IF($C$2:$C$44=C2,$G$2:$G$44),2),"B",IF(G2=SMALL(IF($C$2:$C$44=C2,$G$2:$G$44),3),"C",IF(G2=SMALL(IF($C$2:$C$44=C2,$G$2:$G$44),4),"D",""))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Thank you so much. I can not believe I didn't think of this. You saved me hours of work.
 
Upvote 0
What about these much shorter ones (& no need for C+S+E)?

But please still update your Account details as requested (click your user name at the top right of the forum)

21 05 20.xlsm
CDEFGHI
1RaceValueMaxMin
2573  
3591  
4552 C
5547 A
6555 D
75100C 
8550 B
95117A 
105106B 
11574  
12555 D
13576  
14596D 
15655 C
166120B 
17691  
186119C 
196122A 
20653 B
21660  
22694  
23683  
24656 D
256115D 
26686  
27640 A
28660  
29783  
30778  
31789  
32778  
33760 B
34791  
357111B 
36763 C
37777  
38742 A
39770 D
40779  
417107C 
42799D 
437117A 
44797  
High Low
Cell Formulas
RangeFormula
H2:H44H2=MID("ABCD",SUMPRODUCT(($C$2:$C$44=C2)*(G2<$G$2:$G$44))+1,1)
I2:I44I2=MID("ABCD",SUMPRODUCT(($C$2:$C$44=C2)*(G2>$G$2:$G$44))+1,1)
 
Upvote 0
You're Welcome & Thanks for Feedback. I think Peter Solution is very Simpler, Better & Shorter for Your Situation. Also test it.
 
Upvote 0
Another option if you are using Microsoft 365.

21 05 20.xlsm
CDEFGHI
1RaceValueMaxMin
2573  
3591  
4552 C
5547 A
6555 D
75100C 
8550 B
95117A 
105106B 
11574  
12555 D
13576  
14596D 
15655 C
166120B 
17691  
186119C 
196122A 
20653 B
21660  
22694  
23683  
24656 D
256115D 
26686  
27640 A
28660  
29783  
30778  
31789  
32778  
33760 B
34791  
357111B 
36763 C
37777  
38742 A
39770 D
40779  
417107C 
42799D 
437117A 
44797  
High Low
Cell Formulas
RangeFormula
H2:H44H2=MID("ABCD",MATCH(G2,SORT(FILTER(G$2:G$44,C$2:C$44=C2),,-1),0),1)
I2:I44I2=MID("ABCD",MATCH(G2,SORT(FILTER(G$2:G$44,C$2:C$44=C2)),0),1)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top