Accidents Per Million Miles - How do I write this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craigm - 28 Feb 2006 15:46 GMT

I need to calculate the number of Accidents Per Million miles traveled.

I have a spreadsheet "Mileage" that has total miles per month in
columns:
1,047,178 1,014,900 1,050,481 1,049,958 948,471

I have a spreadsheet "Accidents" that has the total accidents per month
in colums:
8 11 6 16 9

I don't have any idea how to write this macro. I have two years worth
of data and each month new data is entered as totals.

The miles driven data starts in Row 8 column 3 and number of accidents
data starts in row 9 column 3.

Perhaps its the statistics calculations I don't grasp.

Any help would be greatly appreciated!

Thanks in Advance

Craig

Signature

Craigm

Reply to this Message

Gary Keramidas - 28 Feb 2006 16:00 GMT

accidents/(miles/1000000)

Signature

Gary

> I need to calculate the number of Accidents Per Million miles traveled.
>
[quoted text clipped - 19 lines]
>
> Craig

Reply to this Message

Gary Keramidas - 28 Feb 2006 16:05 GMT

sorry, hit send by mistake

Signature

Gary

> accidents/(miles/1000000)
>
[quoted text clipped - 21 lines]
>>
>> Craig

Reply to this Message

Tom Ogilvy - 28 Feb 2006 16:01 GMT

use a formula in row 10 column 3

=C9/C8

then drag fill to the right

Signature

Regards,
Tom Ogilvy

> I need to calculate the number of Accidents Per Million miles traveled.
>
[quoted text clipped - 19 lines]
>
> Craig

Reply to this Message

Tom Ogilvy - 28 Feb 2006 16:12 GMT

sorry, you said per Million Miles. Here is a correction.

=C9/(C8/1000000)

or

=(C9/C8)*1000000

Signature

Regards,
Tom Ogilvy

> use a formula in row 10 column 3
>
[quoted text clipped - 28 lines]
> http://www.excelforum.com/member.php?action=getinfo&userid=24381
> > View this thread: http://www.excelforum.com/showthread.php?threadid=517346

Reply to this Message

Bob Phillips - 28 Feb 2006 16:03 GMT

Just divide # accidents by miles,

=C9/C8

copy the formula across as far as you will ever need. To cater for missing
data, use

=IF(C9="","",C9/C8)

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I need to calculate the number of Accidents Per Million miles traveled.
>
[quoted text clipped - 19 lines]
>
> Craig

Reply to this Message

paul.robinson@it-tallaght.ie - 28 Feb 2006 16:17 GMT

Hi
I'll make two assumptions;
1. By 2 "spreadsheets" you mean 2 worksheets within the same workbook,
one called Mileage and one called Accidents
2. Every mileage entry has a corresponding Accident entry, so your two
worksheets have a range of data with the same number of rows and
columns.

Create a third sheet called "Accidents Per Million Miles"
In Cell A2 of this sheet:
1. Type =
2. Go to the "Accidents" sheet and click on cell C9 (which is where you
say the Accidents data starts)
3. type *1,000,000/
4. Go to the "mileage" sheet and click on cell C8 (which is where you
say the Mileage data starts)
5. Press return
This will give you your accidents per million miles for the first
mileage/accident numbers.

To get all the other accidents per million miles numbers drag the
formula you just created across the required number of columns (click
on the little square at the bottom right of the cell with your mouse
and drag) then, without letting go of the mouseclick, drag down the
formula the number of rows you need.

regards
Paul

Reply to this Message