Finding bottom minimum values

bbran19

New Member
Joined
Jan 30, 2012
Messages
45
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

bbran19

New Member
Joined
Jan 30, 2012
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Thank you so much. I can not believe I didn't think of this. You saved me hours of work.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,325
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback. I think Peter Solution is very Simpler, Better & Shorter for Your Situation. Also test it.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,527
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 
Solution

bbran19

New Member
Joined
Jan 30, 2012
Messages
45
Office Version
  1. 365
Platform
  1. Windows
Done. Thank you for your advice.
 
Last edited:

Forum statistics

Threads
1,141,069
Messages
5,704,111
Members
421,327
Latest member
Msh

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