Ranking on cell value and getting number of unique value

[Navin Agrawal]

Hello friends,

I am trying to do the following in my investment portfolio, need help on a couple of things. Pls see attached excel file.

There are 2 sheets, with 2 different lists of stocks.

Column D = Industry Name
Column E = Stock Name
Column G = Points based on internal analysis

Cell D1 = Formula to get number of unique industries
Cell E1 = COUTNA formula to get number of stocks

Column I = Ranking based on values in column G

Here is where I need help:

#1

Cell D1 = How do I edit this formula so that it can be replicated across sheets with different number of stocks so that I get the right value?

If you notice, Sheet 1 has 53 stocks and Sheet 2 has 46 stocks. I have had to edit the formulas according to number of stocks

Sheet 1 > Cell D1 = {=SUM(1/COUNTIF($D$3:$D$55,$D$3:$D$55))}

Sheet 2 > Cell D1 = {=SUM(1/COUNTIF($D$3:$D$48,$D$3:$D$48))}

If I use the Sheet 1 formula in Sheet 2, it gives an error because the number of stocks is 46 compared to 53 rows...

Let us work with the assumption that there will be a maximum of 200 stocks (rows with sector names)


#2

I need to put a ranking formula in cell I1 and copy it down till the last stock in the portfolio. Here again, I need to keep adjusting the row reference.

Sheet 1 > Cell I1 = RANK(G3,$G$3:$G$55,0)

Sheet 2 > Cell I1 = RANK(G3,$G$3:$G$48,0)

How do I edit the formula in cell I1 so that it automatically takes the row reference by starting from $G$3 to row number based on value in cell E1?

Sheet 1 would be: $G$3:$G$55 (starting row = 3, number of stocks = 53... so it would be 3 + 53 - 1 = 55)

Sheet 2 would be: $G$3:$G$48 (starting row = 3, number of stocks = 46... so it would be 3 + 48 - 1 = 48)

... and the formula uses the COUNTIF function if there are duplicate values in column G

Many thanks in advance...

Regards,
Navin

[AliGW]

Please don't use underscores in your thread titles - it makes them difficult to read. Thanks.

[Navin Agrawal]

Please don't use underscores in your thread titles - it makes them difficult to read. Thanks.

Point noted @Ali... Thanks

[AliGW]

Where have you mocked up some expected results?

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[JohnTopley]

Dynamic Named ranges

e.g industry1

=OFFSET(Sheet1!$D$3,,,COUNTIF(Sheet1!$D$3:$D$300,"<>"),1)

in D1

=SUM(1/COUNTIF(industry1,industry1))[/FORMULA]

points1

=OFFSET(Sheet1!$G$3,,,COUNTIF(Sheet1!$G$3:$G$300,"<>"),1)

in I3

=RANK(G3,Points1,0)

Similar for sheet2

[Navin Agrawal]

Thank you @JohnTopley...

In that case, I will have to define a separate range for each of them on each sheet... I have about 50 - 75 sheets in the file... this would mean defining more than 100 ranges!!!

Is there any other solution which is more standardized to be merely copied on each sheet?

Many thanks...

Regards,
Navin

[JohnTopley]

in D1

=SUM(1/COUNTIF(OFFSET($D$3,,,$E$1),OFFSET($D$3,,,$E$1)))

in I3

=RANK($G$3,OFFSET($G$3,,,$E$1))

[Navin Agrawal]

#8

Thank you @ JohnTopley... This works

However, it does not solve the issue if there is a duplicate value in column G... it returns a duplicate rank instead of giving a next rank...

Regards,
Navin

[AliGW]

Please respond to post #4.

[windknife]

D1 committed with Ctrl+Shift+Enter

=SUM(IF($D$3:$D100="",0,1/COUNTIF($D$3:$D$100,$D$3:$D$100)))

I3 copied down.

=IF(G3="","",COUNTIF($G$3:G100,">="&G3))

[Navin Agrawal]

#9

Thank you @Windknife... This works

However, it does not solve the issue if there is a duplicate value in column G... it returns a duplicate rank instead of giving a next rank...

Regards,
Navin

[JohnTopley]

There are no duplicates in the supplied data so post file with duplicates.

[windknife]

Try this in I3.

=IF(G3="","",COUNTIF($G$3:G100,">"&G3)+COUNTIF($G$3:G3,G3))

[Navin Agrawal]

#12
@JohnTopley: Thank you very much... Query solved...

#13
@windknife: Thank you very much... Query solved...

[Navin Agrawal]

Try this in I3.

=IF(G3="","",COUNTIF($G$3:G100,">"&G3)+COUNTIF($G$3:G3,G3))

@windknife: If I may bother you?

How do I exclude those members from ranking who have ZERO value...

Many thanks

[windknife]

How about this?

=IF(--G3=0,"",COUNTIFS($G$3:G$100,">"&G3,$G$3:$G$100,"<>0")+COUNTIF($G$3:G3,G3))

[Navin Agrawal]

How about this?

=IF(--G3=0,"",COUNTIFS($G$3:G$100,">"&G3,$G$3:$G$100,"<>0")+COUNTIF($G$3:G3,G3))

@windknife

Pls see attached file... The ranking starts from 0 instead of 1

[windknife]

B2

=if(--a2=0,"",countifs(a$2:a$200,">"&a2,a$2:a$200,"<>0")+countif(a$2:a2,a2))

[windknife]

You are welcome.

[AliGW]

Is this what you need?

=IF(OR(G3=0,G3=""),"",COUNTIFS($G$3:G100,">="&G3,$G$3:G100,"<>"&0))

[Navin Agrawal]

#17
@windknife: Thank you very much... It works... Query solved...

#18
@Ali: Thank you very much... It works... Query solved...

[AliGW]

You're welcome.

[windknife]

You are welcome.