Percentage of vehicles speeding?

**Smudge.Smith** · 02-04-2013, 07:00 AM

I have a list of data split into three columns.
The first column is a speed limit for a particular road.
The second column is the speed at which a vehicle was travelling along that road.
The third column is the amount which the vehicle was breaking the speed limit.

I am trying to come up with a formula that will read the content of the cells and produce a percentage for the total amount of vehicles that were travelling at x amount over the posted speed limit.

Eg show the percentage of all drivers who were doing 37mph in a 30mph zone, show me the percentage of all drivers who were doing 39 mph in a 30mph zone.

Eg, show me the percentage of all drivers who were travelling at 47mph in a 40mph zone etc...

Hope someone can help me out

**Fotis1991** · 02-04-2013, 07:14 AM

..how the percentage of all drivers who were doing 37mph in a 30mph zone

Does this ARRAY formula gives you the expected result?

=AVERAGE(IF(A4:A57=30,IF(B4:B57=37,C4:C57)))

**TMS** · 02-04-2013, 07:19 AM

One way, in Excel 2003:

=SUMPRODUCT(--($A$4:$A$57=30),--($B$4:$B$57=30+7))/SUMPRODUCT(--($A$4:$A$57=30))

using 30 as the speed limit and 30+7 = 37 as the actual speed.

30 and 37 could both be cell references, for example:

=SUMPRODUCT(--(A4:A57=D2),--(B4:B57=D3))/SUMPRODUCT(--(A4:A57=D2))

Regards, TMS