ViperGTS Using the FEQUENCY function??

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
Sorry ViperGTS
I screwed your post up when I tried to combine 2 sheets in one here. :)

http://www.mrexcel.com/board/viewtopic.php?topic=22895&forum=2&1

One Function missing, should read;<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE>v:* {behavior:url(#default#VML);}o:* {behavior:url(#default#VML);}x:* {behavior:url(#default#VML);}.shape {behavior:url(#default#VML);}</STYLE><STYLE id=xlfdic01_30638_Styles></STYLE>  <DIV id=xlfdic01_30638 align=center x:publishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 437pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=583 border=0 x:str><COLGROUP><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl2430638 style="WIDTH: 128pt; HEIGHT: 18.75pt" width=171 colSpan=3 height=25>FREQUENCY</TD><TD class=xl2430638 style="WIDTH: 56pt" width=75> </TD><TD class=xl2430638 style="WIDTH: 48pt" width=64> </TD><TD class=xl2430638 style="WIDTH: 48pt" width=64> </TD><TD class=xl2430638 style="WIDTH: 48pt" width=64> </TD><TD class=xl2430638 style="WIDTH: 48pt" width=64> </TD><TD class=xl2430638 style="WIDTH: 61pt" width=81> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl1530638 style="HEIGHT: 13.5pt" height=18></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl3730638>Jan</TD><TD class=xl3730638 style="BORDER-LEFT: medium none">Feb</TD><TD class=xl3730638 style="BORDER-LEFT: medium none">Mar</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl3730638>North</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="5000">£5,000</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="6000">£6,000</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="4500">£4,500</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl3730638 style="BORDER-TOP: medium none">South</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="5800">£5,800</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="7000">£7,000</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="3000">£3,000</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl3730638 style="BORDER-TOP: medium none">East</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="3500">£3,500</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="2000">£2,000</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="10000">£10,000</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl3730638 style="BORDER-TOP: medium none">West</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="12000">£12,000</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="4000">£4,000</TD><TD class=xl3130638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num="6000">£6,000</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3330638> </TD><TD class=xl3530638> </TD><TD class=xl3430638 x:fmla='="Sales "&TEXT(E9,"£#,###")&" and below."'>Sales £4,000 and below.</TD><TD class=xl2630638 align=right x:num="4000">£4,000</TD><TD class=xl3030638 style="BORDER-LEFT: medium none" x:num>4</TD><TD class=xl2830638 colSpan=3><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(D4:F7,E9:E11)}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3330638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3430638 style="BORDER-TOP: medium none" x:str="Sales above £4,000 up to £6,000 " x:fmla='="Sales above "&TEXT(E9,"£#,###")&" up to "&TEXT(E10,"£#,### ")'>Sales above £4,000 up to £6,000</TD><TD class=xl2630638 style="BORDER-TOP: medium none" align=right x:num="6000">£6,000</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>5</TD><TD class=xl2830638 colSpan=3><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(D4:F7,E9:E11)}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3330638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3430638 style="BORDER-TOP: medium none" x:str="Sales above £6,000 " x:fmla='="Sales above "&TEXT(E10,"£#,### ")'>Sales above £6,000</TD><TD class=xl2630638 style="BORDER-TOP: medium none" align=right x:num="999999">£999,999</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>3</TD><TD class=xl2830638 colSpan=3><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(D4:F7,E9:E11)}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl1530638 style="HEIGHT: 13.5pt" height=18></TD><TD class=xl2530638 colSpan=2>What Does It Do ?</TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=6>This function compares a range of data against a list of intervals.</TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=7>The result shows how many items in the range of data fall between the intervals.</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=8>The function is entered in the cells as an array, that is why it is enclosed in { } braces.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl1530638 style="HEIGHT: 13.5pt" height=18></TD><TD class=xl2530638>Syntax</TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=4 x:str="'=FREQUENCY(RangeOfData,ListOfIntervals)">=FREQUENCY(RangeOfData,ListOfIntervals)</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl1530638 style="HEIGHT: 13.5pt" height=18></TD><TD class=xl2530638>Formatting</TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=3>No special formatting is needed.</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl1530638 style="HEIGHT: 13.5pt" height=18></TD><TD class=xl2530638>Example 1</TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=7>The following tables were used to record the weight of a group of children.</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=8>The =FREQUENCY() function was then used to calculate the number of children whose</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638 colSpan=4>weights fell between specified intervals.</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl3730638>Weight Kg</TD><TD class=xl1530638></TD><TD class=xl3330638> </TD><TD class=xl3530638> </TD><TD class=xl3530638> </TD><TD class=xl3630638>Number Of Children:</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638>Child 1</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>20.47</TD><TD class=xl1530638></TD><TD class=xl3330638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3630638 style="BORDER-TOP: medium none">Between 0 - 15 Kg</TD><TD class=xl3030638 style="BORDER-LEFT: medium none" x:num>2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 2</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>22.83</TD><TD class=xl1530638></TD><TD class=xl3330638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3630638 style="BORDER-TOP: medium none">Above 15 but less than or equal to 20 Kg</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 3</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>15.74</TD><TD class=xl1530638></TD><TD class=xl3330638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3530638 style="BORDER-TOP: medium none"> </TD><TD class=xl3630638 style="BORDER-TOP: medium none">Above 20 Kg</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 4</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>10.80</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl2830638 colSpan=4><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(C30:C38,C41:C43)}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 5</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>8.28</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl2830638 colSpan=4><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(C30:C38,C41:C43)}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 6</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>20.66</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl2830638 colSpan=4><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(C30:C38,C41:C43)}</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 7</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>17.36</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 8</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>16.67</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Child 9</TD><TD class=xl3930638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>18.01</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3330638> </TD><TD class=xl3630638>Kg Weight Intervals</TD><TD class=xl1530638></TD><TD class=xl2930638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3230638></TD><TD class=xl2730638 style="BORDER-TOP: medium none" x:num>15</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3230638></TD><TD class=xl2730638 style="BORDER-TOP: medium none" x:num>20</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl3230638></TD><TD class=xl2730638 style="BORDER-TOP: medium none" x:num>100</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl1530638 style="HEIGHT: 12.75pt" height=17></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="PAGE-BREAK-BEFORE: always; HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl2530638>Example 2</TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD><TD class=xl2530638> </TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=4>This example uses characters instead of values.</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=7>A restaurant has asked 40 customers for their rating of the food in the restaurant.</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=6>The ratings were entered into a table as a single letter, E, V, A, P or D.</TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=7>The manager now wants to calculate how many responses fell into each category.</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=8>Unfortunately, the =FREQUENCY() function ignores text entries, so how can the frequency</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=2>of text be calculated?</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=5>The answer is to use the =CODE() and =UPPER() functions.</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=7>The =UPPER() forces all the text entries to be considered as capital letters.</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=7>The =CODE() function calculates the unique ANSI code for each character.</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638 colSpan=7>As this code is a numeric value, the =FREQUENCY() function can then be used!</TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638></TD><TD class=xl3730638>Rating</TD><TD class=xl3730638 style="BORDER-LEFT: medium none">Frequency</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl3730638>Excellent</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">E</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>6</TD><TD class=xl4030638 colSpan=5><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Very Good</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">V</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>8</TD><TD class=xl4030638 colSpan=5><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Average</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">A</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>9</TD><TD class=xl4030638 colSpan=5><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Poor</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>8</TD><TD class=xl4030638 colSpan=5><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl3730638 style="BORDER-TOP: medium none">Disgusting</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">D</TD><TD class=xl3030638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" x:num>9</TD><TD class=xl4030638 colSpan=5><SPAN style="mso-spacerun: yes"> </SPAN>{=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl3830638 colSpan=2>Customer Ratings</TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl2730638>V</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">D</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">V</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">A</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">p</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">A</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">D</TD><TD class=xl2730638 style="BORDER-LEFT: medium none">D</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl2730638 style="BORDER-TOP: medium none">V</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">a</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">D</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">A</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">V</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">d</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl2730638 style="BORDER-TOP: medium none">A</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">V</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">E</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">p</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">E</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">D</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">A</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl2730638 style="BORDER-TOP: medium none">A</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">E</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">d</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">V</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">D</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">a</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">E</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl2730638 style="BORDER-TOP: medium none">V</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">e</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">P</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">A</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">V</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">E</TD><TD class=xl2730638 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">D</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl1530638 style="HEIGHT: 12pt" height=16></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD><TD class=xl1530638></TD></TR></TBODY></TABLE></DIV>
_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails
image.gif

This message was edited by Ivan F Moala on 2002-09-20 17:57
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-09-20 17:38, tim963 wrote:
Ivan, how do you post a page like that to to board?? Thanks

Hi tim963
It is done via the Latest Addin that Colo and I are developing for the Board.
Works with Excel2000 +
This is just one of the features of the Addin
 

Hero-0952

Wizard Deluxe
Joined
Jun 11, 2002
Messages
348
Ivan!

Wow! You and Aladin have just set me up great! This is good stuff - and I did visit the http://homepage.ntlworld.com/noneley site. I downloaded the file but haven't looked at it yet.

I was afraid that I wouldn't be able to use the Frequency function as I (initially) thought it was too restrictive but, it appears as though it will work better than the =COUNTIF and conditional formatting mess that I have going on now.

Wonderful and Thank You!

ViperGTS
 

Forum statistics

Threads
1,144,058
Messages
5,722,269
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top