3

I've imported a shapefile of the US and set a projection. I also added an Excel spreadsheet with income data from 1995-2010. I joined the income data with shapefile based on the state_name field.

What I would like to display is the percent increase or decrease over those ten years. Is there a way to do this in Layer Properties | Symbology, or do I need to do the calculation in Excel before importing and joining?

6

You could calculate the field beforehand in Excel, or you can also do this in ArcMap. You need to

  1. add a field to your table (make sure you aren't in an edit session, thanks Sara). This will be for the % change.

  2. calculate the percent change in that field, using the Field Calculator. Possibly something like:

    ((table.INCOME_2005 - table.INCOME_1995)/ table.INCOME_1995) * 100
    
  3. Use the field you created in the symbology window.

5
  • 2
    Under #1 - if you are in an edit session, you cannot add a field to your table. Oct 14 '14 at 15:55
  • 1
    One thing to watch out for: there is no automatic updating calculations in fields. If you edit or add new values, you'll need to perform the calculation again. It's not like a field with formulas in Excel.
    – recurvata
    Oct 14 '14 at 16:08
  • 2
    Also, you sure about the parentheses there? The division will be done first, resulting in 1 being subtracted from the first figure. I think you may want: (table.INCOME_2005 - table.INCOME_1995) / table.INCOME_1995 * 100 although I'd skip the * 100 myself and either format the field as percent or just use the decimal form.
    – recurvata
    Oct 14 '14 at 16:49
  • I added the field Percent_1, type = short integer with a precision of 1.
    – BillTheCat
    Oct 14 '14 at 17:44
  • You should use a float or double for percent, not integer.
    – recurvata
    Oct 14 '14 at 20:30
1

I would generally recommend having the ratio in a field, especially since in this case you're interested in a proportional change between two years.

A general alternative to calculating, however, is to use Symbology. It's useful if you expect the data values to change, more data to be added, or just don't want to add/calculate a new field.

This is discussed briefly in the Using Graduated Colors help page:

Optionally, select a Normalization field to normalize the data. The values in this field will be used to divide the Value field to create ratios.

So in this example below, I'm symbolizing my data as population per square mile:

screenshot of symbology dialog using normalization

1
  • Wahooooo! Big thanks to everyone. I learned something new.
    – BillTheCat
    Oct 14 '14 at 18:35
1

I too would suggest using column/formula in Excel. I am assuming that you have columns labeled like:

INCOME_1995, INCOME_1996, INCOME_1997, etc

I would suggest appending another set of columns labeled like:

PRCHNG_1996, PRCHNG_1997, etc (note no need for 1995 since it would be 0%)

I would then assign a formula to the second set of columns such that they compute:

(( income_current_year - income_1995 ) / income_1995) * 100.00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.