I see you took care of my first point on your own... you might still want to
consider incorporating my second point.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You will need to modify this formula as it requires a letter entry in
Column E whereas the OP said it was possible for this column to not have a
letter in it. Also, you might want to allow for nothing to be displayed
for empty rows so the user can copy your formula down through blank rows
(in anticipation of future row entries). I'm thinking of something like
this...

=IF(A1="","",<<your formula)

--
Rick (MVP - Excel)


"Max" wrote in message
...
Another option ..
In F2, copied down:
=SUM(VLOOKUP(B2,{0,0;20,1;31,2;41,3;51,4},2),C2,(D 2="x")*3,VLOOKUP(E2,{"A",5;"B",4;"C",3;"D",2;"E",1 },2,0))
Success? Ring the stars in google
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
<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