Difference between revisions of "Manuals/calci/RANKANDPERCENTILE"

From ZCubes Wiki
Jump to navigation Jump to search
(Created page with "<div id="6SpaceContent" class="zcontent" align="left"> '''RANKANDPERCENTILE'''(Array, GroupBy, NewTableFlag) where, '''Array '''- Input range should be one block. ...")
 
Line 1: Line 1:
−
<div id="6SpaceContent" class="zcontent" align="left">
+
<div style="font-size:30px">'''RANKANDPERCENTILE(ar,gr,lv)'''</div><br/>
 +
*<math> ar </math> is the array value to find rank and percentile.
 +
*<math> gr </math> is the grouped by value.
 +
*<math> lv </math> is the logical value.
  
−
'''RANKANDPERCENTILE'''(Array, GroupBy, NewTableFlag)
 
  
−
where,
+
==Description==
 +
*This function gives the rank and percentile value of a given set of numbers.
 +
*Ranking  is giving the order from highest to lowest of the given set.
 +
*Percentile allowing to easily categorise the given data into percentiles .
 +
*This function is used to analyze the relative standing of values in a data set.
 +
*In <math> RANKANDPERCENTILE(ar,gr,lv), ar </math> is the array of the numeric values to find rank and percentile.
 +
*<math> gr </math> is the grouped by value. <math> gr </math> value is either columns or rows.
 +
*<math> lv </math> is the logical value either TRUE or FALSE. TRUE is indicating the result will display in new worksheet.
 +
*Suppose we are omitted the lv value it will consider the value as FALSE.
 +
*This function is giving the result in four columns.
 +
  1. First  column showing the indices(position in the given set) of the ordered data from largest to smallest number.
 +
  2. The second column showing the  data values according to the indices in the first column.
 +
  3. The third column showing the rank value of the data value in the second column.
 +
  4. The fourth column indicates the  the percentile of the data value in the second column.
  
−
'''Array '''- Input range should be one block.
+
==Examples==
 +
5
 +
11
 +
6
 +
9
 +
15
 +
7
 +
=RANKANDPERCENTILE(B1:B6,columns,true)
  
−
'''GroupBy '''- Either groupby Columns or Rows.
 
  
−
'''NewTableFlag''' - is the TRUE or FALSE.If set as TRUE,the result in new sheet. If NewTableFlag is omitted, it assumed to be FALSE.
+
==See Also==
 +
*[[Manuals/calci/RANK | RANK]]
 +
*[[Manuals/calci/PERCENTILE  | PERCENTILE ]]
 +
*[[Manuals/calci/PEARSON | PEARSON ]]
  
−
</div>
 
−
----
 
−
<div id="1SpaceContent" class="zcontent" align="left">Creates a list of the numbers ranked from highest to lowest and also provides a percentile value which indicates how an individual number ranks compared to the other numbers.</div>
 
−
----
 
−
<div id="7SpaceContent" class="zcontent" align="left">
 
  
−
Lets see an example in (Column3Row1)
+
==References==
−
 
 
−
<nowiki>=RANKANDPERCENTILE(R1C1:R6C2, "Rows", TRUE)</nowiki>
 
−
 
 
−
RANKANDPERCENTILE returns the result in new sheet(5Space).
 
−
 
 
−
<nowiki>=RANKANDPERCENTILE(R1C1:R6C1, "Columns", TRUE)</nowiki>
 
−
 
 
−
RANKANDPERCENTILE returns the result in new sheet(9Space).
 
−
 
 
−
</div>
 
−
----
 
−
<div id="12SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="left">
 
−
 
 
−
RANK AND PERCENTILE AVERAGE
 
−
 
 
−
</div></div>
 
−
----
 
−
<div id="10SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Syntax </div><div class="ZEditBox"><center></center></div></div>
 
−
----
 
−
<div id="4SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Remarks </div></div>
 
−
----
 
−
<div id="3SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Examples </div></div>
 
−
----
 
−
<div id="11SpaceContent" class="zcontent" align="left"><div class="ZEditBox" align="justify">Description </div></div>
 
−
----
 
−
<div id="8SpaceContent" class="zcontent" align="left"></div>
 
−
----
 
−
<div id="2SpaceContent" class="zcontent" align="left">
 
−
 
 
−
{| id="TABLE3" class="SpreadSheet blue"
 
−
|- class="even"
 
−
| class=" " |
 
−
| Column1
 
−
| Column2
 
−
| class="  " | Column3
 
−
| class="    SelectTD1 ChangeBGColor SelectTD1" |
 
−
<div id="2Space_Handle" class="zhandles" title="Click and Drag to resize CALCI Column/Row/Cell. It is EZ!"></div><div id="2Space_Copy" class="zhandles" title="Click and Drag over to AutoFill other cells."></div><div id="2Space_Drag" class="zhandles" title="Click and Drag to Move/Copy Area.">[[Image:copy-cube.gif]]  </div>Column4
 
−
|- class="odd"
 
−
| class=" " | Row1
 
−
| 5
 
−
| 3
 
−
| class="sshl_f" | 5Space
 
−
| class=" " |
 
−
|- class="even"
 
−
| class="  " | Row2
 
−
| 7
 
−
| 8
 
−
| class="    " |
 
−
| class="      1" |
 
−
|- class="odd"
 
−
| Row3
 
−
| 12
 
−
| 5
 
−
| class=" " |
 
−
| class=" " |
 
−
|- class="even"
 
−
| Row4
 
−
| 17
 
−
| 18
 
−
| class=" " |
 
−
| class=" " |
 
−
|- class="odd"
 
−
| class=" " | Row5
 
−
| 41
 
−
| 31
 
−
| class=" " |
 
−
| class=" " |
 
−
|- class="even"
 
−
| Row6
 
−
| 6
 
−
| 9
 
−
| class="sshl_f" | 9Space
 
−
| class=" " |
 
−
|}
 
−
 
 
−
<div align="left">[[Image:calci1.gif]]</div></div>
 
−
----
 
−
<div id="5SpaceContent" class="zcontent" align="left">
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Row1
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 1
 
−
| 5
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 2
 
−
| 3
 
−
| 2
 
−
| 0
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Row2
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 2
 
−
| 8
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 1
 
−
| 7
 
−
| 2
 
−
| 0
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Row3
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 1
 
−
| 12
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 2
 
−
| 5
 
−
| 2
 
−
| 0
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Row4
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 2
 
−
| 18
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 1
 
−
| 17
 
−
| 2
 
−
| 0
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Row5
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 1
 
−
| 41
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 2
 
−
| 31
 
−
| 2
 
−
| 0
 
−
|}
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Row6
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 2
 
−
| 9
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 1
 
−
| 6
 
−
| 2
 
−
| 0
 
−
|}
 
−
 
 
−
</div>
 
−
----
 
−
<div id="9SpaceContent" class="zcontent" align="left">
 
−
 
 
−
{| class="SpreadSheet blue"
 
−
|+ <br />Rank And Percentile
 
−
|- class="even"
 
−
! Point
 
−
! Column1
 
−
! Rank
 
−
! Percent
 
−
|- class="odd"
 
−
| 5
 
−
| 41
 
−
| 1
 
−
| 100
 
−
|- class="even"
 
−
| 4
 
−
| 17
 
−
| 2
 
−
| 80
 
−
|- class="odd"
 
−
| 3
 
−
| 12
 
−
| 3
 
−
| 60
 
−
|- class="even"
 
−
| 2
 
−
| 7
 
−
| 4
 
−
| 40
 
−
|- class="odd"
 
−
| 6
 
−
| 6
 
−
| 5
 
−
| 20
 
−
|- class="even"
 
−
| 1
 
−
| 5
 
−
| 6
 
−
| 0
 
−
|}
 
−
 
 
−
</div>
 
−
----
 

Revision as of 10:28, 20 January 2014

RANKANDPERCENTILE(ar,gr,lv)


  • is the array value to find rank and percentile.
  • is the grouped by value.
  • is the logical value.


Description

  • This function gives the rank and percentile value of a given set of numbers.
  • Ranking is giving the order from highest to lowest of the given set.
  • Percentile allowing to easily categorise the given data into percentiles .
  • This function is used to analyze the relative standing of values in a data set.
  • In is the array of the numeric values to find rank and percentile.
  • is the grouped by value. value is either columns or rows.
  • is the logical value either TRUE or FALSE. TRUE is indicating the result will display in new worksheet.
  • Suppose we are omitted the lv value it will consider the value as FALSE.
  • This function is giving the result in four columns.
 1. First  column showing the indices(position in the given set) of the ordered data from largest to smallest number.
 2. The second column showing the  data values according to the indices in the first column.
 3. The third column showing the rank value of the data value in the second column.
 4. The fourth column indicates the  the percentile of the data value in the second column.

Examples

5 11 6 9 15 7 =RANKANDPERCENTILE(B1:B6,columns,true)


See Also


References

Retrieved from "http://wiki.zcubes.com/index.php?title=Manuals/calci/RANKANDPERCENTILE&oldid=197299"