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
ublishsource="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
This message was edited by Ivan F Moala on 2002-09-20 17:57
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

_________________
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

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