I would like to thank everybody who so generously gave of their knowledge and time in helping us with this project. The formula that we ended up using was the second one provided by Max, which was this:

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

One reason I particularly liked it was that even with my very highly limited familiarity with Excel, I was able to adapt it to include more values for the points assigned to accidents, to change the values so that they did not necessarily increase by 10 each time (because it was thought that high-accident locations should get progressively more weight), and to add a value for the symbol 20 in that column. The formula as actually used therefore looked more like this:

=SUM(VLOOKUP(B2,{"<20",0;0,0;20,1;31,2;40,3;49,4;5 7,5;64,6},2),C2,(D2="x")*3,IF(E2="",0,(VLOOKUP(E2, {"A",5;"B",4;"C",3;"D",2;"E",1},2,0))))

Because of everyone's help, the project was completed quickly and the report is now ready to be given on time to Police Commissioner Kelly -- who is not a patient man.

As an aside, I note that the question was asked "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!"

The short answer is "not really." While the NYPD has among its 50,000 employees people who are highly proficient in all sorts of technological wizadry, they are not usually found in the field commands. When a field command is given the job of producing a report within one or two days, that command does not have the luxury of turning to another unit and saying "here, do our assigned work for us", nor is it possible to say "we don't have the technological skill to do this in the most modern and effective way, so instead of giving you this report Tuesday, we will wait the eight to twelve months it takes to approve, and hire for, a new civil-service civilian position -- assuming that the creation of a new non-patrol position IS approved and paid for by the City." (And it might also help to know that America's largest police department is also one of the most low-tech; many records are not computerized, e-mail is simply not used AT ALL for internal department communications, some offices do not even have any computer access of any kind, we still use typed forms with carbon paper inserts, and the primary and most important record kept in any of our 76 precincts remains the Command Log, which is kept by hand, in ink, in a large ledger book.) Under such circumstances, one does the best job one can with the resources one has available, regardless of how limited they actually are, or how much more efficient we would be if we had the resources we might have if we all lived in an ideal world.

As it so happened, one of the resources that WAS available to us in our very real world was the body of knowledge that can be found on a forum such as this. Through the generous sharing of that knowledge, we were able to complete our project at the time we needed to complete it, using only the personnel available for its completion -- and for that, we are thoroughly grateful.



Posted as a reply to:

Who can help the NYPD with an Excel formula?
20-Sep-09

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
WPF And The Model View View Model Pattern
http://www.eggheadcafe.com/tutorials...l-view-vi.aspx