MAX IF

K

kloy

New Member
Joined
Aug 15, 2005
Messages
24
i have a large spreadsheet (aprox 20.000 rows) of data.
column A contains the company name
column B contains the sector
column C contains sales-values

i would like to list the top 3 companies of every sector.

BUT:
- i can not ensure that the file is sorted by sales-values
- and it is (theoretically, though) possible that some sales-values are identical for different companies

is there a pure-excel solution for this problem?

i thought about getting the row-number out of the MAX-function... (the MATCH function requires the spreadsheet to be sorted)

thank you for every input!

ps: i didn't know that microsoft is even translating the function names into the different languages. i'm working on a dutch system:
SUMIF=SOM.ALS
MATCH=ZOEKEN
AVERAGE=GEMIDDELDE
:)
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Welcome to the board!

Here is one way:


F1: =LARGE(IF($B$3:$B$20000=F$3,$C$3:$C$20000),3)
Confirm with Ctrl + shift + Enter and drag right

F2: =SUMPRODUCT(--($B$3:$B$20000=F3),--($C$3:$C$20000>=F1))
Normal enter and drag right

F4: =IF(ROW()-ROW(F$3)<=F$2,INDEX($A$1:$A$20000,SMALL(IF(($B$3:$B$20000=F$3)*($C$3:$C$20000>=F$1),ROW($A$3:$A$20000)),ROW()-ROW(F$3))),"")
Confirm with Ctrl + shift + Enter and drag right / down.
Book1.xls
ABCDEFGHI
1Limit191220
2CompanySectorSalesReturns435
3Comp1A10ABC
4Comp2B11Comp20Comp7Comp24
5Comp3C12Comp21Comp8Comp25
6Comp4A13Comp22Comp9Comp26
7Comp5A14Comp23 Comp28
8Comp6A15  Comp29
9Comp7B14   
10Comp8B13   
Sheet1
 
Upvote 0
Edit to above:

I forgot to arrange returns in descending order.

Change formula in F4 to

=IF(ROW()-ROW(F$3)<=F$2,INDEX($A$3:$A$20000,MATCH(LARGE(IF(($B$3:$B$20000=F$3)*($C$3:$C$20000>=F$1),$C$3:$C$20000+(ROW($C$3:$C$20000)/100000)),ROW()-ROW($F$3)),$C$3:$C$20000+ROW($C$3:$C$20000)/100000,0)),"")

Confirm with Ctrl + shift + Enter and drag right / down.
 
Upvote 0
Given the sheer amount data along with multiple sectors, you migh want to consider the pivot table approach, where Sector is a page field...
Book6
ABCDEFG
1Company NameSectorSalesSectorS2
2C4S150
3C2S260Sum of Sales
4C3S150Company NameTotal
5C7S265C260
6C8S155C765
7C8S255C855
8C5S250Grand Total180
9C1S160
10
Sheet1


You can then display Top 3 companies per sector thru the Advanced option under Field Settings.
 
Upvote 0
Hi Aladin!

I first tried to adapt one of your Top N posts to this but Excel nearly choked on the RANK formula going 20000 rows down, while the formulas above seems to calculate acceptably fast. Any suggestions?
 
Upvote 0
fairwinds said:
Hi Aladin!

I first tried to adapt one of your Top N posts to this but Excel nearly choked on the RANK formula going 20000 rows down, while the formulas above seems to calculate acceptably fast. Any suggestions?
Click to expand...

That surprises me (but considering 20000 formulas with CountIf...). In order to apply the formula system for Top N, we first need the data sets by sectors and then apply the formula system to the singled out sector data (that's also the way it's done by the Pivot Table functionality). If the OP insists on using formulas, this would be the way I'd go.
 
Upvote 0
Ok, thanks.

I tried to rank regardless of sector and add condition to the retrieving formulas. However the retrieving formulas did not cause performance problems.

=RANK(C3,$C$3:$C$20000)+COUNTIF(C3:$C$3,C3)-1

did, when applied to 20000 rows...
 
Upvote 0
fairwinds said:
Ok, thanks.

I tried to rank regardless of sector and add condition to the retrieving formulas. However the retrieving formulas did not cause performance problems.

=RANK(C3,$C$3:$C$20000)+COUNTIF(C3:$C$3,C3)-1

did, when applied to 20000 rows...
Click to expand...

Yes, I just checked it (without timing). Constructing ranks with this formula takes too long after the range size > X. Would be nice to know that X.
 
Upvote 0
wow!
incredible.

i entered all the formulas and they did what i wanted, thank you!

my next step is to understand formula F4... it is quite tricky.

i am amezed.
 
Upvote 0
today i tried the pivot-table approach, quite handy indeed, but i get an error when using the list of companies: too much unique items!...

but i have what i wanted.
 
Upvote 0
You must log in or register to reply here.

Similar threads

WESTERNWALL
  • Question
Compare lists of employees on two spreadsheets using email address as unique identifiers
Replies
4
Views
297
Peter_SSs
Peter_SSs
gtd526
  • Solved
Average specific value if listed on any worksheet
Replies
7
Views
394
Tetra201
Tetra201
H
  • Question
How to filter for a unique value.
Replies
5
Views
340
RobP
R
C
  • Question
Microsoft Advanced Filter works only intermittently. Same exact filter in another workbook with same exact layout doesn't work.
Replies
2
Views
205
c_bro2498
C
H
  • Question
Need a button function to share a workbook with colleague
Replies
0
Views
314
HMC62015
H

Forum statistics

Threads
1,215,472
Messages
6,125,011
Members
449,204
Latest member
tungnmqn90

Share this page

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