I'm trying to perform a sum of the number of incidents in
a certain range of mileposts. I have all the MP in one
column, and have simplified them down to half mile
increments (i.e. 215, 215.5, etc.) using a statement that
said =CEILING(C15705,0.5). I then wrote a statement that
said =IF(AD15705<AD15706,COUNT($AD$15705:AD15705),"").
What I want it to do is to start the count over for each
different MP section (215-215.5 and then another count for
215.5-216 for example).

Is there a way to do this? Helpful e-mails may be directed
to my e-mail account.

Thanks,
Christina

Christina

Where is your data, what range? Give a small sample of your data and its
layout. Then show what you want the result to be for that sample.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christina" wrote in message
...
I'm trying to perform a sum of the number of incidents in
a certain range of mileposts. I have all the MP in one
column, and have simplified them down to half mile
increments (i.e. 215, 215.5, etc.) using a statement that
said =CEILING(C15705,0.5). I then wrote a statement that
said =IF(AD15705<AD15706,COUNT($AD$15705:AD15705),"").
What I want it to do is to start the count over for each
different MP section (215-215.5 and then another count for
215.5-216 for example).

Is there a way to do this? Helpful e-mails may be directed
to my e-mail account.

Thanks,
Christina

The columns in question are Milepost (C), MP at 0.5 mile Increments
(AC), MP at 1.0 mile Increments (AD), Accident Counts by 0.5 mi Starting
at 215 (AE), Accident Counts
by 1.0 mi Starting at 215 (AF). The rows are from 15705 to 23583.

Column C, Milepost, has multiple mileposts which I need to sort accident
data by 0.5 mile increments and 1.0 mile increments. Therefore, I used
the formulas I mentioned before.

The result I would like to achieve is knowing the total amount of
accidents in the 0.5 increments and the 1.0 increments. I was using a
count, but I don't know how to get it to start over automatically. Right
now, I've been hand changing it.

I could make a screenshot, but I can't post that here. Please let me
know if this is enough information and in the right format. Thank you so
much for your help.

Christina



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Christina

Let me see if I can clarify. You have mileposts in column C. They are not
in any order and there may be duplicates or omissions. Basically, Col C is
one milepost per accident. It could look like this

215
215
215
216
217
217

which would mean that there were three accidents at 215, one at 216, and two
at 217. How precise are the mileposts in column C, tenths, hundredths?

Then in AC you have every half-milepost whether there was an accident or
not. In AE you want to know how many accidents occurred in that half mile.
If your Col C data starts in 15705 and your data in AE starts in 15705, then
try this formula in AE15705

=COUNTIF($C$15705:$C$23583,"<"&AC15705)-SUM($AE$15704:AE15704)

This will count all the values that are less than the half-mile post and
subtract any values that have already been counted (less than the previous
half mile post.

If that's way off, you can email the workbook or a screenshot to me.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Christina" wrote in message
...
The columns in question are Milepost (C), MP at 0.5 mile Increments
(AC), MP at 1.0 mile Increments (AD), Accident Counts by 0.5 mi Starting
at 215 (AE), Accident Counts
by 1.0 mi Starting at 215 (AF). The rows are from 15705 to 23583.

Column C, Milepost, has multiple mileposts which I need to sort accident
data by 0.5 mile increments and 1.0 mile increments. Therefore, I used
the formulas I mentioned before.

The result I would like to achieve is knowing the total amount of
accidents in the 0.5 increments and the 1.0 increments. I was using a
count, but I don't know how to get it to start over automatically. Right
now, I've been hand changing it.

I could make a screenshot, but I can't post that here. Please let me
know if this is enough information and in the right format. Thank you so
much for your help.

Christina



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

That worked great! Thanks for interpreting my lack of information or
data. Thank you so much for helping me out.

Christina


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!