I am trying to summarize data for an operations program. The operations
admin has a worksheet "Data" where she will enter individual events that
required attention as line items.

There are several "Districts" that fall under the scope of Operations that
can receive awards based on the number of days they have gone without any new
events. Each event receives a new line item in the Data worksheet, and the
name of the District is recorded in Column G.

I made a worksheet called "Frequencies" where I have built a table that
reports the longest span of time each district has gone without a safety
event. Here is the calculation for the "Admin" district:

=MAX(IF((Data!A2:A2500=2007)*(Data!G2:G2500="Admin ")*(Data!J2:J2500<"FA"),Data!C2:C2500))

The "Max" value is coming from Column C of the "Data" worksheet. Column C
on the data worksheet is a calculation that subtracts the date of the
previously entered record (The date is stored D2), from the current record
(D3). It is a simple calculation: =D3-D2.

This will work perfectly unless the admin decides to enter dates out of
order, which could result in a negative value being returned in Cell C3.

What I need to do is to correct what is going on in Column C of the "Data"
worksheet. Instead of subtracting the date of the previous record from the
date of the current record- I'd like the calculation in Column C to look at
All dates in Column D, cells D2:D2500, find the date that is closest to, but
previous to the date of the current record, and subtract that date from the
current date- and return that value.




"John C" wrote:

What is your little calculation trying to do, actually? It will impact
earlier calculations as well. Once we know what you are trying to accomplish,
we can go forward.
--
John C


"Gina" wrote:

I have possibly screwed up in a big way.

In my spreadsheet I have assumed that records would be added chronologically
(as they should), and did not allow for the fact that people don't always do
as they should.

Which means, my little calculation in Cell C615 ( =D615-D614) could actually
return a negative number if the items were not data entered in Chronological
order.

I'm not sure whether the formula in Cell C615 should be altered to say- look
at the date in Cells D002:D2500 and find the one with a date nearest, but
less than cell D615- and subtract that date from cell D615 and return the
value to Cell C615--- or if I should somehow prevent records from being
entered out of order.

Any ideas or help would be very greatly appreciated.

Gina