paulclark wrote:
I have a spreadsheet full of clients from different cities, and I'd like to create a chart (3D ideally) showing which cities have the most clients and which have the fewest.
For example, 158 live in City A, 132 City B all the way down to 1 client in City V.
Currently my spreadsheet has first, last, street, ZIP, city, state, misc info A, misc info B, misc info C Etc Etc.
I may also like to create a chart showing totals of one of the Misc columns.
Use a subsidiary table to collect the names of the cities in your list, and to count them. Chart the results. Here's an example.
Cities are listed in column B, and may be in any order. Spelling counts, as the formula in column E identifies each city by the exact contents of the cell.
Column E contains a formula that counts the number of different entries in column B, and marks the row containing the first occurrence of each with a serial number.
The formula in E2 is
=IF(COUNTIF($B$1:B2,B)=1,MAX($E$1:E1)+1,"")
Fill down to the end of the column.
Column A in the second table (For Graph) contains a formula that constructs a reference to the cell containing the first occurrence of each City name on the Main table, and copies that name to it's cell.
The formula in A2 is
=IFERROR(OFFSET(Main :: $A$1,MATCH(ROW()-1,Main :: $E,0)-1,1),"")
Fill down to the end of the column.
Column B contains a formula that counts all occurrences in column B of the Main table of the name in its row in column A.
The formula in B2 is
=IF(COUNTIF(Main :: B,A2)>0,COUNTIF(Main :: B,A2),"")
Fill down to the end of the column.
Regards,
Barry
PS: Descriptions of each function used, and further examples of their use can be found in the iWork Formulas and Functions User Guide. You can download the guide (and the Numbers '09 User Guide) through the Help menu in Numbers.
B
