Ranking on cell value and getting number of unique value

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

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

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.

Dynamic Named ranges

e.g industry1

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

in D1

Formula:

Please Login or Register  to view this content.

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

points1

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

in I3

Formula:

Please Login or Register  to view this content.

Similar for sheet2

Originally Posted by AliGW

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

Point noted @Ali... Thanks

Please respond to post #4.

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

in D1

Formula:

Please Login or Register  to view this content.

in I3

Formula:

Please Login or Register  to view this content.

#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

#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

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

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

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

Originally Posted by windknife

Try this in I3.

Please Login or Register  to view this content.

@windknife: If I may bother you?

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

Many thanks

Is this what you need?

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

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

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

You're welcome.

Originally Posted by windknife

How about this?

Please Login or Register  to view this content.

@windknife

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

#22

Thank you @windknife... it works now... Appreciate it...