Proportional Problem in Excel

Dear Class:

It was truly fun being in lab today! I had a blast. Sorry that I had to dart off. Unfortunately, this will be the case every lab session, as I have to lecture at 1:10 in St. Paul on Wednesdays. However, I will have office hours on Thursdays or Tuesdays -- whichever is more convenient for you -- in the lab too. I will be there tomorrow after lecture. Now, an important note for those of you using your own data. If you are using the West Nile data, you need not read further.

---------------------------
Today I showed you how to determine the circle sizes for your proportional symbols using Excel. However, I quickly realized that what I showed you may not work perfectly for those of you using your own data. The reason being: my smallest value was "1"; so every other value merely had to be multiplied by one. For example, Ohio was the lowest with "1", and Minnesota had "16." Thus, Minnesota's circle size was obviously 16-times (16*) the area of Ohio's circle.

This will not be the case if your lowest value is any number other than "1." Let's pretend your lowest number is "5" and your next lowest number is "7". Seven is not seven times the size of five. It is only 40% larger than five.

Below I will show some screen captures that illustrate how to program Excel to remedy this situation. If you have any questions regarding my directions, please feel free to email me. Thanks and sorry for any inconvenience.

Whereas I told you to take a state and multiply its number of West Nile cases by the area of the lowest valued state's circle, instead you need to first figure out the proportional difference between the lowest state's number of West Nile cases and the state you are trying to figure out.

Originally, I had you type in the second lowest state's "Circle Area" column <=B23*$C$24>. B was the column labeled "West Nile Cases" and $C$24 was in the "Area" column, the lowest valued state (Ohio).

Well, to correct for the fact that not every sample starts with a value of one, you should instead type: <=B23/$B$24*$C$24>

What this does is divide your current state's value by the lowest value to get a percentage of increase. It then multiplies this percentage of increase time the smallest circle area to give you a proper proportional circle. Please email me if you have questions. Email me your Excel sheet too, and I will show you using your very own data!

Thanks again for your patience.

Setting up the smallest value does not change at all.
The area of the smallest value should be equal to your chosen radius squared times Pi (3.14).
Please see image below.

Originally, I told you to multiply the data value for each forthcoming case by the circle area for the smallest case. This works if your smallest value is 1. But only then! (See below.)



You should replace the above formula for area (for all of your cases aside from the one with the smallest value, which does not change) with this formula below. Divide the value of your current case by the value of your smallest case and multiply that value times the area of the smallest case. (Please see formula below.)