Subtotal - Max and Min at one time?

[nancystorey]

I have a lot of lines of data to compare. I need to find the min and max of a series of numbers. The first 6 digits are the same, but each range could include 2-10 numbers to compare. I have been using SUBTOTAL with the MAX function, and then again with the MIN function (not replacing subtotals) but with the amount of data to process (58k+ rows), it takes hours for each step.

1st - is there a way to do both functions in one process?

2nd - is there an easier way to do this? I am lost! Thanks!


2013530 0.0057

2013531 0.0057

2013532 0.0057

2013534 0.0057

2013535 0.0066

2013536 0.0057

2013537 0.0057

2013538 0.0019

2013539 0.0057

 

[NARAYANK991]

Hi Nancy ,


If you are not averse to using helper columns , you can try out the following :


1. Assume your data is in the range A2:B100 ( if your data extends beyond row 100 , just copy the formulae as far as your data extends ).


2. You can use column C as the helper column , and have the MAXIMUM value in column D , and the MINIMUM value in column E.


3. In cell C2 , enter the following formula :

[pre]

=LEFT(A2,6)
4.  In cell D2 , enter the following formula , as an array formula ( using CTRL SHIFT ENTER ) :

[pre][code]=IF(C2=C3,"",SMALL(--(LEFT($A$2:$A$100,6)=LEFT(A2,6))*($B$2:$B$100),COUNT($B$2:$B$100)-SUM(IF(--(LEFT($A$2:$A$100,6)=LEFT(A2,6))>0,1,0))+1))

[/pre]
5. In cell E2 , enter the following formula , as an array formula ( using CTRL SHIFT ENTER ) :

=IF(C2=C3,"",LARGE(--(LEFT($A$2:$A$100,6)=LEFT(A2,6))*($B$2:$B$100),1))[/code][/pre]
Copy all the above three formulae as far down as your data ; if your data falls below or goes beyond row 100 , adjust all the address references to 100 to whatever is relevant for your data row ; for example , if your data extends to row 3476 , replace all instances of 100 by 3476.


Narayan

 

[NARAYANK991]

Hi Nancy ,


The long formula given in (4) above , can be shortened to the following :


=IF(C2=C3,"",SMALL(--(LEFT($A$2:$A$100,6)=LEFT(A2,6))*($B$2:$B$100),SUM(IF(--(LEFT($A$2:$A$100,6)=LEFT(A2,6))=0,1,0))+1))


Narayan

 

[Hui]

You could also use Text to Columns

Separate the first six numbers out

Then pass it through a Pivot Table and display Min & Max

 

[nancystorey]

wow, thanks so much, both of you. I'll give these a try!