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