In Excel 2003 I have four long columns 10,000 + rows. A=country, B=city name,
C= year D=city population.
My analysis has two levels of priority. First a way to change a formula that
recognises the date range in col C to track the cities in col D for the next
change in date and RANK them (i.e. E $ 80: E $ 88 being an example of an
array of cites in the same date range) in the sample test for a formula: =
RANK (E80, IF (AND (E80 0, A80 = A79, C80 = C79), $ 80 E: E $ 88,0))
The number of cities is not the same for each date or for each country.
A second priority which would use one or more macros would carry out the
following steps:
1. Sort the table by column A 'country'
2. Sort by col C date for each country.
3. Sort col D city (population 000s) by size for each date
4. Calculate the RANK of every city in each date and country (- the first
priority)
5. Create a column or other means of eliminating the city name NATION from
the Ranking as this is the country total population .
5. Calculate the log N for Rank and population for each city
6. Compute, a) the slope, b) coefficient of determination and c) the ratio
of city population ranked 1, on adding city (2 +3) / 2, for each country and
date.
Grateful for any suggestion that can help simplifying the scope of this
exercise.