rank in excel

[JIGYANSHA1985]

Sir,
I have an excel sheet. In that sheet what I would like to reflect is equal rank for equal status (i.e. a/c or deno) but If A/c or Deno are zero or less then that then no rank shall be given. Main is the master sheet from which datas shall be taken for ranking. There are two types of ranking ONEDAY ( 2 fields in the main sheet are :- daydly_ac & daydly_amt for A/C & DENO) and the other one is TOTAL ( 2 fields in the main sheet are :- dly_ac & dly_amt for A/C & DENO). Ranks shall be given from highest to lower. In this way one should find easily which branch is topper. A sample is attached for your reference.
Regards
Jigyansha

[HansV]

In B3 on the Oneday sheet, enter the formula

=IF(D3=0,"",RANK(D3,$D$3:$D$109))

and fill down to B109. Similar for G3, and for the Total sheet.

[JIGYANSHA1985]

Sir,
Sorry to disturb you once again. But I want that at B3 rank shall be given depending upon d3 and accordingly at c3 corresponding name of the branch shall be reflected ... Hope a macro would be a better sollution to this ...

Regards
Jigyansha

[HansV]

See the attached version. Everything is done with formulas. If the data change, you only need to sort the tables again.

dly_rank.zip

[JIGYANSHA1985]

Sir,
The rank sheet that provided by you, the ranks are in some cells not in order for ex. After rank 5 comes 6 but it here showing 7, like wise after 11 showing 13, after 13 showing 17, after 18 showing 22 and so on.
Again It would be better that if the ranks should be given after the numbers(a/cs & deno) are sorted discendingly. But equal ranks for equal number. That doesn't mean that in ranking procedure any ranking number should skip.
Regards
Jigyansha

[HansV]

That's the way Excel's RANK function works. If there are 0 rows with a higher value, the rank is 1; if there is 1 row with a higher value, the rank is 2. It there are two rows with rank 5, the next one will have rank 7 because there are 6 rows with a higher value:

RANK	BRANCH NAME	A/C	Explanation for rank
1	BARGARH	33	0 rows with higher value
2	BHUBANESWAR-I SECTOR	28	1 rows with higher value
3	CUTTACK SECTOR	25	2 rows with higher value
4	SAMBALPUR SECTOR	20	3 rows with higher value
5	NIMAPARA	18	4 rows with higher value
5	ANGUL SECTOR	18	4 rows with higher value
7	JEYPORE SECTOR	17	6 rows with higher value

Trying to do it differently makes things more difficult.

[JIGYANSHA1985]

But, if the ranks comes like this by skipping 1 or 2 position then it may not be the exact answer. Is the macro a solution to this ?

[HansV]

But, if the ranks comes like this by skipping 1 or 2 position then it may not be the exact answer.

That depends on how you interpret the results.

[JIGYANSHA1985]

Sir,
Is there any way that my answers should come as per my excel sheet as provided to you earlier ...

Regards
Jigyansha

[HansV]

Does the attached version do what you want?

dly_rank.zip

[JIGYANSHA1985]

When new records or datas are placed at main sheet, On Reflection sheet answer is something different. This is not in sorted order... The file attached once again for your reference ...

[HansV]

In the attached version, the sheets are automatically sorted when activated.

I'll let you solve the problem with the negative score in Main!DD4 yourself.

dly_rank.zip

[JIGYANSHA1985]

Extremely Sorry for the delay in my reply ... The problem in one trial seems to be solved ... But very soon I'll let you know whether the same works perfectly in huge database ...