Who can help the NYPD with an Excel formula?

[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!


EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx

 

[Rick Rothstein]

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.

 

[Max]

Another option ..
In F2, copied down:
=SUM(VLOOKUP(B2,{0,0;20,1;31,2;41,3;51,4},2),C2,(D2="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
---

 

[Max]

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,(D2="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
---

 

[Rick Rothstein]

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 Rothstein]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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/tutorial...a-6dafb17b6d74/wcf-workflow-services-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

 

[Ron Rosenfeld]

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/tutorial...a-6dafb17b6d74/wcf-workflow-services-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

 

[Rick Rothstein]

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 Rothstein]

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

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 Rothstein]

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

 

[Max]

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

 

[Bernd P]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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/tutorial...a-6dafb17b6d74/wcf-workflow-services-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

 

[Rick Rothstein]

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 Rothstein]

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

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.

 

[Bernd P]

Hello Rick,

Agreed and thanks for your corrections.

Regards,
Bernd

 

[Bill R]

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/tutorial...a-6dafb17b6d74/wcf-workflow-services-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