Accidents Per Million Miles - How do I write this?

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

accidents/(miles/1000000)

--


Gary


"Craigm" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
> http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=517346
>

use a formula in row 10 column 3

=C9/C8

then drag fill to the right


--
Regards,
Tom Ogilvy


"Craigm" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=517346
>

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)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Craigm" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile:
http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=517346
>

sorry, hit send by mistake

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com> wrote in message
news:[email protected]...
> accidents/(miles/1000000)
>
> --
>
>
> Gary
>
>
> "Craigm" <[email protected]> wrote in
> message news:[email protected]...
>>
>> 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
>>
>>
>> --
>> Craigm
>> ------------------------------------------------------------------------
>> Craigm's Profile:
>> http://www.excelforum.com/member.php...o&userid=24381
>> View this thread: http://www.excelforum.com/showthread...hreadid=517346
>>
>
>

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

=C9/(C8/1000000)

or

=(C9/C8)*1000000

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> use a formula in row 10 column 3
>
> =C9/C8
>
> then drag fill to the right
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Craigm" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
> >
> > --
> > Craigm
> > ------------------------------------------------------------------------
> > Craigm's Profile:
> http://www.excelforum.com/member.php...o&userid=24381
> > View this thread:
http://www.excelforum.com/showthread...hreadid=517346
> >
>
>

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