Accidents Per Million Miles - How do I write this?

C

Craigm

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
 
B

Bob Phillips

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)
 
T

Tom Ogilvy

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

=C9/(C8/1000000)

or

=(C9/C8)*1000000

--
Regards,
Tom Ogilvy

Tom Ogilvy said:
use a formula in row 10 column 3

=C9/C8

then drag fill to the right
 
P

paul.robinson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top