<GreenWhiteBlue wrote in message ...
The Transportation Bureau at the New York City Police Department has lots
of people who know all sorts of things about moving traffic, but none of
us knows much about using Excel in anything more than the most basic ways.
I am hoping that someone out there can help us create a formula that will
be used to improve traffic safety and traffic movement in the largest city
in the US.

Here is the problem: the NYPD assigns Traffic Enforcement Agents to
intersections based on a point system, with intersections accumulating
point totals based on a variety of factors. Right now, we caluculate this
total by hand, but a proper Excel formula would certainly make this
easier. The required formula must be able to add various things, including
the point values assigned to letters, and the points assigned to
individual values in a range.

On our worksheet, intersections are listed by row, with each row being one
intersection. We are trying to create a "total" column for each
intersection.

Let us assume that we are adding the values in row 2.

Cell A2 contains the name of the intersection, which obviously does not
get added to anything.

In cell B2, we have recorded the number of accidents at the intersection.
Point values for those acidents are derived from a range. If the
intersection had 20 to 30 accidents, it gets 1 point; if it had 31 to 40
accidents, it gets 2 points, if it had 41 to 50 accidents, it gets 3
points, and if it had 51 or more accidents it gets 4 points.

In cell C2, we record the number of bus routes that pass through the
intersection. Each bus route gets 1 point, so we are just adding whatever
number appears in this cell.

In cell D2, we place an X if the intersection meets one of several
conditions, such as proximity to a hospital, or to a tourist attraction. A
cell with an X gets 3 points, but no X gets nothing.

Finally, in cell E2, we indicate the intersection's importance as a
highway access point. This importance is indicated by a letter ranging
from A through E. Class A gets 5 points, B gets 4 points, C gets 3, D gets
2 and E gets 1 point. If there is no letter, the intersection gets no
points.

Based on the information above, what would the exact formula look like in
cell F2, where we want the total points to appear?

In addition, will this formula work in both Excel 2003 and Excel 2007
(both of which are available for use, but both of which are not on
everyone's computers)?

Thanks to all in advance for the assistance!


EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorials...vices-usi.aspx

Can't NYPD afford to contract someone to analyse requirements and produce a
working model. It should be much more efficient, reliable (and probably
more effective) than a home-brew effort!

Bill R