GreenWhiteBlue

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!

macropod

Hi GWB,

Try:
=MIN(MAX(INT((B2-1)/10)-1,0),5)+C2+(D2="X")*3+(E2="A")*5+(E2="B")*4+(E2="C")*3+(E2="D")*2+(E2="E")*1
The above should work in any version of Excel.

GreenWhiteBlue

Macropod, thank you very much for that. I also asked the same question on another forum, and I was also very kindly given this answer, which is the one that I ended up using:

=SUM(VLOOKUP(B2,{0,0;20,1;31,2;41,3;51,4},2),C2,(D2="x")*3,IF(E2="",0,(VLOOKUP(E2,{"A",5;"B",4;"C",3;"D",2;"E",1},2,0))))

Everybody's assistance was very much appreciated, and the report we needed has now been successfully completed for submission to the Police Commissioner.

macropod

Hi GWB,

Please see: http://www.excelguru.ca/node/7