Array formula problem

[AusSteelMan]

Hi everyone,

I would like to count the number of occurances of a number in a list. The list is generated by the RANK formula as shown below.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: center">19</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: center">20</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: center">=21</TD><TD style="TEXT-ALIGN: right">54</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: center">=21</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: center">23</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: center">24</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J22</TD><TD>=RANK(H22,$H$2:$H$55,1)</TD></TR><TR><TD>K22</TD><TD>=IF(OR((J22=J23),(J22=J21)),"="&J22,J22)</TD></TR><TR><TD>L22</TD><TD>{=COUNT(IF($J$2:$J$55=J22,1,0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!



</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Col J is the RANK value
Col K shows where a RANK is achieved more than once, but relies on the RANK column being in order.
I would prefer another column to be in order, but would still like to know where a RANK has been attained more than once. I figured that if I COUNTed the number of times a RANK is attained, then I could use this number to determine if an "=" is required in a simple IF statement.

I'd like it to look something like this:

Sheet2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">=21</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">23</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">24</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">20</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">=21</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">19</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


I have tried a couple of CSE formulas, but cannot quite get it right.

Any help offered is greatly appreciated.

Thanks,
Darren

 

[AusSteelMan]

OK I got it.

For interest and posterity...

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: center">19</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: center">20</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: center">=21</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: center">=21</TD><TD style="TEXT-ALIGN: center">=21</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: center">=21</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: center">=21</TD><TD style="TEXT-ALIGN: center">=21</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">23</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">24</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">24</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J22</TD><TD>=RANK(H22,$H$2:$H$55,1)</TD></TR><TR><TD>K22</TD><TD>=IF(OR((J22=J23),(J22=J21)),"="&J22,J22)</TD></TR><TR><TD>L22</TD><TD>{=COUNT(IF($J$2:$J$55=J22,1))}</TD></TR><TR><TD>M22</TD><TD>=IF(L22=1,J22,"="&J22)</TD></TR><TR><TD>N22</TD><TD>{=IF((COUNT(IF($J$2:$J$55=J22,1)))=1,J22,"="&J22)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Col J is the RANK value
Col K compares above and below to determine if an "=" is required, but needs J to be sorted
Col L is the working CSE to count the number of occasions that rank is attained.
Col M uses Col L to determine if an "=" is required
Col N combines into one formula

So when Col J is not the sorted column, the result is:

Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">14</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">52</TD><TD style="TEXT-ALIGN: center">52</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: center">=52</TD><TD style="TEXT-ALIGN: center">=52</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">36</TD><TD style="TEXT-ALIGN: center">36</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">36</TD><TD style="TEXT-ALIGN: center">36</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">44</TD><TD style="TEXT-ALIGN: center">44</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">44</TD><TD style="TEXT-ALIGN: center">44</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">17</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">17</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">43</TD><TD style="TEXT-ALIGN: center">43</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: center">43</TD><TD style="TEXT-ALIGN: center">43</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J22</TD><TD>=RANK(H22,$H$2:$H$55,1)</TD></TR><TR><TD>K22</TD><TD>=IF(OR((J22=J23),(J22=J21)),"="&J22,J22)</TD></TR><TR><TD>L22</TD><TD>{=COUNT(IF($J$2:$J$55=J22,1))}</TD></TR><TR><TD>M22</TD><TD>=IF(L22=1,J22,"="&J22)</TD></TR><TR><TD>N22</TD><TD>{=IF((COUNT(IF($J$2:$J$55=J22,1)))=1,J22,"="&J22)}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

So now I see that Col N works as required
Col K does not work and can be deleted
Col L & M can be deleted

Regards,
Darren