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

I believe this formula in F2 will do what you asked...

=IF(A2="","",MIN(4,INT((MAX(0,B2-10))/10))+C2+3*(D2="X")+IF(E2="",0,70-CODE(UPPER(E2))))

The assumptions it works under is that Column E will only have the letters
A, B, C, D, E in it or the cell will be blank.

This formula can be copied down as needed. You can copy it down through
empty rows in anticipation of future entries if you want to.

--
Rick (MVP - Excel)


"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

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

Slight refinement to handle col E = blank
In F2, copied down:
=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))))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

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

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

On Sun, 20 Sep 2009 19:37:29 -0400, "Rick Rothstein"
wrote:

I believe this formula in F2 will do what you asked...

=IF(A2="","",MIN(4,INT((MAX(0,B2-10))/10))+C2+3*(D2="X")+IF(E2="",0,70-CODE(UPPER(E2))))

The assumptions it works under is that Column E will only have the letters
A, B, C, D, E in it or the cell will be blank.

This formula can be copied down as needed. You can copy it down through
empty rows in anticipation of future entries if you want to.

I believe your formula is giving incorrect answers with regard to accident
counts "on the margin".

The OP specified:

20 to 30 accidents, it gets 1 point
31 to 40 accidents, it gets 2 points
41 to 50 accidents, it gets 3 points
51 or more accidents it gets 4 points.

Your formula seems to give one more point than indicated at 30, 40 and 50
--ron

On Sun, 20 Sep 2009 16:09:03 -0700, GreenWhiteBlue wrote:

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


Try this:

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(UPPER(D2)="X")+
IF(ISNA(MATCH(UPPER(E2),{"E","D","C","B","A"},0)), 0,
MATCH(UPPER(E2),{"E","D","C","B","A"},0))

--ron

On Sun, 20 Sep 2009 16:09:03 -0700, GreenWhiteBlue wrote:

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

My previous formula can be shortened. The UPPER function is not necessary.

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
IF(ISNA(MATCH(E2,{"E","D","C","B","A"},0)),0,
MATCH(E2,{"E","D","C","B","A"},0))
--ron

Correction to handle the values in the accident column correctly...

=IF(A2="","",MIN(4,(B2=20)+INT((MAX(0,B2-11))/10))+C2+3*(D2="X")+IF(E2="",0,70-CODE(UPPER(E2))))

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I believe this formula in F2 will do what you asked...

=IF(A2="","",MIN(4,INT((MAX(0,B2-10))/10))+C2+3*(D2="X")+IF(E2="",0,70-CODE(UPPER(E2))))

The assumptions it works under is that Column E will only have the letters
A, B, C, D, E in it or the cell will be blank.

This formula can be copied down as needed. You can copy it down through
empty rows in anticipation of future entries if you want to.

--
Rick (MVP - Excel)


"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

I believe this formula in F2 will do what you asked...

=IF(A2="","",MIN(4,INT((MAX(0,B2-10))/10))+C2+3*(D2="X")+IF(E2="",0,70-CODE(UPPER(E2))))

The assumptions it works under is that Column E will only have the letters
A, B, C, D, E in it or the cell will be blank.

This formula can be copied down as needed. You can copy it down through
empty rows in anticipation of future entries if you want to.

I believe your formula is giving incorrect answers with regard to accident
counts "on the margin".

The OP specified:

20 to 30 accidents, it gets 1 point
31 to 40 accidents, it gets 2 points
41 to 50 accidents, it gets 3 points
51 or more accidents it gets 4 points.

Your formula seems to give one more point than indicated at 30, 40 and 50

Thanks... I just posted a correction. I had gotten thrown by the different
range size between 20-30 and the rest of the ranges.

--
Rick (MVP - Excel)

My previous formula can be shortened. The UPPER function is not
necessary.

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
IF(ISNA(MATCH(E2,{"E","D","C","B","A"},0)),0,
MATCH(E2,{"E","D","C","B","A"},0))

I think it can be shortened even further to this...

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
MATCH(E2&" ",{" ","E ","D ","C ","B ","A "},0)-1

--
Rick (MVP - Excel)

As-is, think the revised expression can be copied down. It would simply
return zeros for any unfilled/new rows which result I believe would be
acceptable. My aim was to keep all things direct and easy-to-relate for the
OP with respect to the incorporation of each of the specs for the various
cols in the core expression. For eg in using simple vlookups with hardcoded
table arrays, the OP can see how the specified limits are directly taken
care of. Anyway, its just an option to calc the desired result. There are
undoubtedly several other ways.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Rick Rothstein" wrote in message
...
I see you took care of my first point on your own... you might still want
to consider incorporating my second point.

Hello,

I suggest not to focus on a short, but on a maitainable solution for
somebody who is not too fluent with this kind of stuff:
=LOOKUP(B2,{0,21,31,41,51},{0,1,2,3,4})+C2+3*(D2=" X")+FIND(LEFT(E2&" ",
1);" EDCBA")-1

Regards,
Bernd

On Sun, 20 Sep 2009 22:41:42 -0400, "Rick Rothstein"
wrote:

My previous formula can be shortened. The UPPER function is not
necessary.

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
IF(ISNA(MATCH(E2,{"E","D","C","B","A"},0)),0,
MATCH(E2,{"E","D","C","B","A"},0))

I think it can be shortened even further to this...

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
MATCH(E2&" ",{" ","E ","D ","C ","B ","A "},0)-1

I like your suggestion. Also, it will return an error for an illegal entry in
column E, which I believe is preferable.
--ron

On Sun, 20 Sep 2009 16:09:03 -0700, GreenWhiteBlue wrote:

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

And, with Rick's suggested modification, to mine, it is not only shorter, but
will also return an error message should there be an illegal entry in column E:

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
MATCH(E2&" ",{" ","E ","D ","C ","B ","A "},0)-1
--ron

Just a couple of comments. First, you need to change your 21 to 20 in the
LOOKUP function (the OP's ranges were not all the same). Second, you have a
semi-colon in the FIND function that I think should be a comma. Third, I
would suggest using SEARCH instead of FIND so the user can use upper or
lower case letters in Column E as they wish. Implementing these makes your
formula this...

=LOOKUP(B2,{0,20,31,41,51},{0,1,2,3,4})+C2+3*(D2=" X")+
SEARCH(LEFT(E2&" ",1)," EDCBA")-1

One other thing I would suggest is to add a check to see if the row has a
name in Column A and, if not, output an empty string, like this...

=IF(A2="","",LOOKUP(B2,{0,20,31,41,51},{0,1,2,3,4} )+
C2+3*(D2="X")+SEARCH(LEFT(E2&" ",1)," EDCBA")-1)

This will allow the user to copy the formula down past the end of their
current data. With your original formula, copy it down past the end of the
current data will display 0's in Column E cells on the empty rows. Using the
modified formula, nothing will be displayed until entries are made in the
row; otherwise, the user will have to remember to copy the formula down
every time they enter a new row of data. (Note: I know you know this
Bernd... I wrote it for the benefit of the OP should they decide to use your
offering.)

--
Rick (MVP - Excel)


"Bernd P" wrote in message
...
Hello,

I suggest not to focus on a short, but on a maitainable solution for
somebody who is not too fluent with this kind of stuff:
=LOOKUP(B2,{0,21,31,41,51},{0,1,2,3,4})+C2+3*(D2=" X")+FIND(LEFT(E2&" ",
1);" EDCBA")-1

Regards,
Bernd

I think it can be shortened even further to this...

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
MATCH(E2&" ",{" ","E ","D ","C ","B ","A "},0)-1

I like your suggestion. Also, it will return an error for an illegal
entry in
column E, which I believe is preferable.

I agree about the benefit of returning the error as well. The only
modification I would suggest is to test Column A for an entry and return the
empty string if it is empty; that way, the OP can copy the formula down past
the end of the current data (anticipating future entries) without having it
display zeroes...

=MIN(4,INT((MAX(0,B2-11)+(B2=20))/10))+C2+3*(D2="X")+
MATCH(E2&" ",{" ","E ","D ","C ","B ","A "},0)-1

With that said, I kind of like Bernd's suggested formula (coupled with my
suggested modifications) a little better than either of our formulas.

--
Rick (MVP - Excel)

Hello Rick,

Agreed and thanks for your corrections.

Regards,
Bernd

<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

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