Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10t h
Stage1,2,2,0,1,3,4,5,6,7,8,9
Stage2,4,4,2,0,1,3,5,6,7,8,9
Stage3,0,0,4,2,1,3,5,6,7,8,9
Stage4,2,2,0,4,1,3,5,6,7,8,9
Stage5,2,2,0,4,1,3,5,6,7,8,9
Stage6,1,2,1,0,4,3,5,6,7,8,9
Stage7,4,2,4,1,0,3,5,6,7,8,9
Stage8,2,2,4,1,0,3,5,6,7,8,9
Stage9,4,2,4,1,0,3,5,6,7,8,9
Stage10,0,2,4,0,1,3,5,6,7,8,9
Stage11,7,2,4,0,7,1,3,5,6,8,9
Stage12,0,2,0,4,7,1,3,5,6,8,9
Stage13,1,2,0,4,1,7,3,5,6,8,9
Stage14,7,2,0,4,7,1,3,5,6,8,9
Stage15,9,2,0,4,7,1,9,3,5,6,8

I have the above data in range A1:L16. Data in range A1:B16 is the real data
and data in range C2:L16 is the work that I did it manually. Actually I am
looking for formulas in the range C2:L16.

I want to rank the numbers in column B based on their frequencies for all
the stages. For example, number in cell B2 is 2 hence the number 2 should
hold 1st rank therefore I have put number 2 in cell C2 for 1st rank.

When you move to stage2, you should consider both stage1 and stage2. For
example, in Stage2, both the numbers 2 and 4 have appeared once, in this case
the number which is at the bottom should get 1st rank therefore I have put
number 4 in cell C3 for 1st rank and the number 2 in cell D3 for second rank.

Whenever two or more numbers are of the same frequencies, then take the
number at the bottom of the range/array as the top rank. For example, in
Stage12 (range B2:B13) , the number 2 has the highest frequency (4x), hence
1st rank (cell C13). Number 0 and 4 both appeared thrice but the number 0 is
at the bottom of the range hence rank 2nd (cell D13)for 0 and rank 3rd for 4
(cell E13). Number 1 and 7 appreared once but 7 is at the bottom of the range
hence rank 4th for 7 (cell F13) and rank 5th for 1 (cell G13).

If a particular number is not appeared even once, then rank them in
ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not appear
even once, hence I have arranged them in ascening order for their ranks
(range H13:L13)

I tried to use temporary columns with the RANK worksheet function with
combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I am not
able to work it out. I also tried writing a VBA code but I am getting
confused on counters.

A formula based solution will be appreciated but if it is not possible, then
excel vba/macro will also do.

Can anybody help me?

Try the following, which requires that you insert a blank column between
Column B and Column C...

1) First, let...

A1:B16 contain your data

Column C remain blank

D1:M1 contain the column labels for the ranking

2) Then, define the following names...

Select D2

Insert Name Define

Name: Array

Refers to:

=COUNTIF(MyRange,MyRange)+ROW(MyRange)/10^10

Click Add

Name: MyRange

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$16,ROWS(Sheet1!D $2:D2))

Click Add

Name: Nums

Refers to:

={0,1,2,3,4,5,6,7,8,9}

Click Add

Name: UniqueArray

Refers to:

=IF(MyRange<"",IF(MATCH(MyRange,MyRange,0)=ROW(My Range)-MIN(ROW(MyRange)
)+1,Array))

Click Add

Name: UniqueCount

Refers to:

=COUNT(1/(FREQUENCY(MyRange,MyRange)0))

Click Ok

*Change the sheet reference accordingly.

3) Then, use the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

D2, copied down and across:

=IF(COLUMNS($D2:D2)<=UniqueCount,INDEX(MyRange,MAT CH(LARGE(UniqueArray,CO
LUMNS($D2:D2)),Array,0)),INDEX(Nums,MATCH(0,COUNTI F($C2:C2,Nums),0)))

Hope this helps!

In article ,
"mac_see" wrote:

Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10t h
Stage1,2,2,0,1,3,4,5,6,7,8,9
Stage2,4,4,2,0,1,3,5,6,7,8,9
Stage3,0,0,4,2,1,3,5,6,7,8,9
Stage4,2,2,0,4,1,3,5,6,7,8,9
Stage5,2,2,0,4,1,3,5,6,7,8,9
Stage6,1,2,1,0,4,3,5,6,7,8,9
Stage7,4,2,4,1,0,3,5,6,7,8,9
Stage8,2,2,4,1,0,3,5,6,7,8,9
Stage9,4,2,4,1,0,3,5,6,7,8,9
Stage10,0,2,4,0,1,3,5,6,7,8,9
Stage11,7,2,4,0,7,1,3,5,6,8,9
Stage12,0,2,0,4,7,1,3,5,6,8,9
Stage13,1,2,0,4,1,7,3,5,6,8,9
Stage14,7,2,0,4,7,1,3,5,6,8,9
Stage15,9,2,0,4,7,1,9,3,5,6,8

I have the above data in range A1:L16. Data in range A1:B16 is the real data
and data in range C2:L16 is the work that I did it manually. Actually I am
looking for formulas in the range C2:L16.

I want to rank the numbers in column B based on their frequencies for all
the stages. For example, number in cell B2 is 2 hence the number 2 should
hold 1st rank therefore I have put number 2 in cell C2 for 1st rank.

When you move to stage2, you should consider both stage1 and stage2. For
example, in Stage2, both the numbers 2 and 4 have appeared once, in this case
the number which is at the bottom should get 1st rank therefore I have put
number 4 in cell C3 for 1st rank and the number 2 in cell D3 for second rank.

Whenever two or more numbers are of the same frequencies, then take the
number at the bottom of the range/array as the top rank. For example, in
Stage12 (range B2:B13) , the number 2 has the highest frequency (4x), hence
1st rank (cell C13). Number 0 and 4 both appeared thrice but the number 0 is
at the bottom of the range hence rank 2nd (cell D13)for 0 and rank 3rd for 4
(cell E13). Number 1 and 7 appreared once but 7 is at the bottom of the range
hence rank 4th for 7 (cell F13) and rank 5th for 1 (cell G13).

If a particular number is not appeared even once, then rank them in
ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not appear
even once, hence I have arranged them in ascening order for their ranks
(range H13:L13)

I tried to use temporary columns with the RANK worksheet function with
combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I am not
able to work it out. I also tried writing a VBA code but I am getting
confused on counters.

A formula based solution will be appreciated but if it is not possible, then
excel vba/macro will also do.

Can anybody help me?

Thanks Domenic

"Domenic" wrote:

Try the following, which requires that you insert a blank column between
Column B and Column C...

1) First, let...

A1:B16 contain your data

Column C remain blank

D1:M1 contain the column labels for the ranking

2) Then, define the following names...

Select D2

Insert Name Define

Name: Array

Refers to:

=COUNTIF(MyRange,MyRange)+ROW(MyRange)/10^10

Click Add

Name: MyRange

Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$16,ROWS(Sheet1!D $2:D2))

Click Add

Name: Nums

Refers to:

={0,1,2,3,4,5,6,7,8,9}

Click Add

Name: UniqueArray

Refers to:

=IF(MyRange<"",IF(MATCH(MyRange,MyRange,0)=ROW(My Range)-MIN(ROW(MyRange)
)+1,Array))

Click Add

Name: UniqueCount

Refers to:

=COUNT(1/(FREQUENCY(MyRange,MyRange)0))

Click Ok

*Change the sheet reference accordingly.

3) Then, use the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

D2, copied down and across:

=IF(COLUMNS($D2:D2)<=UniqueCount,INDEX(MyRange,MAT CH(LARGE(UniqueArray,CO
LUMNS($D2:D2)),Array,0)),INDEX(Nums,MATCH(0,COUNTI F($C2:C2,Nums),0)))

Hope this helps!

In article ,
"mac_see" wrote:

Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10t h
Stage1,2,2,0,1,3,4,5,6,7,8,9
Stage2,4,4,2,0,1,3,5,6,7,8,9
Stage3,0,0,4,2,1,3,5,6,7,8,9
Stage4,2,2,0,4,1,3,5,6,7,8,9
Stage5,2,2,0,4,1,3,5,6,7,8,9
Stage6,1,2,1,0,4,3,5,6,7,8,9
Stage7,4,2,4,1,0,3,5,6,7,8,9
Stage8,2,2,4,1,0,3,5,6,7,8,9
Stage9,4,2,4,1,0,3,5,6,7,8,9
Stage10,0,2,4,0,1,3,5,6,7,8,9
Stage11,7,2,4,0,7,1,3,5,6,8,9
Stage12,0,2,0,4,7,1,3,5,6,8,9
Stage13,1,2,0,4,1,7,3,5,6,8,9
Stage14,7,2,0,4,7,1,3,5,6,8,9
Stage15,9,2,0,4,7,1,9,3,5,6,8

I have the above data in range A1:L16. Data in range A1:B16 is the real data
and data in range C2:L16 is the work that I did it manually. Actually I am
looking for formulas in the range C2:L16.

I want to rank the numbers in column B based on their frequencies for all
the stages. For example, number in cell B2 is 2 hence the number 2 should
hold 1st rank therefore I have put number 2 in cell C2 for 1st rank.

When you move to stage2, you should consider both stage1 and stage2. For
example, in Stage2, both the numbers 2 and 4 have appeared once, in this case
the number which is at the bottom should get 1st rank therefore I have put
number 4 in cell C3 for 1st rank and the number 2 in cell D3 for second rank.

Whenever two or more numbers are of the same frequencies, then take the
number at the bottom of the range/array as the top rank. For example, in
Stage12 (range B2:B13) , the number 2 has the highest frequency (4x), hence
1st rank (cell C13). Number 0 and 4 both appeared thrice but the number 0 is
at the bottom of the range hence rank 2nd (cell D13)for 0 and rank 3rd for 4
(cell E13). Number 1 and 7 appreared once but 7 is at the bottom of the range
hence rank 4th for 7 (cell F13) and rank 5th for 1 (cell G13).

If a particular number is not appeared even once, then rank them in
ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not appear
even once, hence I have arranged them in ascening order for their ranks
(range H13:L13)

I tried to use temporary columns with the RANK worksheet function with
combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I am not
able to work it out. I also tried writing a VBA code but I am getting
confused on counters.

A formula based solution will be appreciated but if it is not possible, then
excel vba/macro will also do.

Can anybody help me?