Restrict Data Values Allowed
 

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

Restrict Data Values Allowed
 

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

Restrict Data Values Allowed
 

Option 1
Search Help for Data Form - it will automatically add new rows at the bottom
of your data. Train your users to employ it

Option 2
Sort your data and then copy/paste your formulas

"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

Restrict Data Values Allowed
 

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

Restrict Data Values Allowed
 

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.

Given the above, I think you could have a bigger problem then you realize.
You are concerned with a negative value being generated from the calculation
=D615-D614, but if the entries are not being made in chronological order, it
would seem the the values that need to be subtracted could be separated by
several rows rather than be simply in reverse order. If that is the case, I
would think it would make any calculations based on this formula incorrect,
right?

Rick


"Gina" wrote in message
...
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

Restrict Data Values Allowed
 

That's exactly right.

"Rick Rothstein (MVP - VB)" wrote:

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.

Given the above, I think you could have a bigger problem then you realize.
You are concerned with a negative value being generated from the calculation
=D615-D614, but if the entries are not being made in chronological order, it
would seem the the values that need to be subtracted could be separated by
several rows rather than be simply in reverse order. If that is the case, I
would think it would make any calculations based on this formula incorrect,
right?

Rick


"Gina" wrote in message
...
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

Restrict Data Values Allowed
 

I just realized I've done this absolutely and completely wrong. The cell in
C3 can't just subtract the date of D2 from D3, because they could be entirely
different districts, and what I am trying to count is the number of days that
pass between line items that report events- by district.

Then on the other worksheet tab- Frequencies- I'm trying to find the max
value of column C, by district.

I was definitely more wrong than I originally thought.

"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

Restrict Data Values Allowed
 

All is not lost! You don't even have to change your sheets, just a few
formulas. I have taken the assumption that the district is in column B of
your main spreadsheet (which I will name Data for this example).
I am also assuming your data starts in row 2, and goes to row 1000. Modify
as needed.

In C2, which will calculate the day difference in days as you originally
intended.
C2:
=IF(COUNTIF($B1:$B$1,B2)<0,D2-MAX(IF(($B$2:$B$1000=B2)*($D$2:$D$1000<D2),$D$2:$D $1000)),"")
NOTE: One small glitch, the first entry of each new district SHOULD be the
first chronologically, beyond that, the formula really doesn't care!

Copy down as needed. Ensure the format of the column is General (or number
if you prefer).

On your 'tally sheet', assuming your District is in column B again, your
formula to calculate the longest time frame would be entered as follows:
=LARGE(IF((Data!$B$2:$B$1000=B2),$C$2:$C$1000),1)

Both formulas are array** formulas, so be sure to press CTRL+Shift+Enter to
commit the formula!
--
John C


"Gina" wrote:

I just realized I've done this absolutely and completely wrong. The cell in
C3 can't just subtract the date of D2 from D3, because they could be entirely
different districts, and what I am trying to count is the number of days that
pass between line items that report events- by district.

Then on the other worksheet tab- Frequencies- I'm trying to find the max
value of column C, by district.

I was definitely more wrong than I originally thought.

"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

Restrict Data Values Allowed
 

I'm trying to make this work but it's acting a little goofy. I'll keep
tweaking and write back a little later today. I really appreciate your
trying to help, because I've been completely stumped.
Gina

"John C" wrote:

All is not lost! You don't even have to change your sheets, just a few
formulas. I have taken the assumption that the district is in column B of
your main spreadsheet (which I will name Data for this example).
I am also assuming your data starts in row 2, and goes to row 1000. Modify
as needed.

In C2, which will calculate the day difference in days as you originally
intended.
C2:
=IF(COUNTIF($B1:$B$1,B2)<0,D2-MAX(IF(($B$2:$B$1000=B2)*($D$2:$D$1000<D2),$D$2:$D $1000)),"")
NOTE: One small glitch, the first entry of each new district SHOULD be the
first chronologically, beyond that, the formula really doesn't care!

Copy down as needed. Ensure the format of the column is General (or number
if you prefer).

On your 'tally sheet', assuming your District is in column B again, your
formula to calculate the longest time frame would be entered as follows:
=LARGE(IF((Data!$B$2:$B$1000=B2),$C$2:$C$1000),1)

Both formulas are array** formulas, so be sure to press CTRL+Shift+Enter to
commit the formula!
--
John C


"Gina" wrote:

I just realized I've done this absolutely and completely wrong. The cell in
C3 can't just subtract the date of D2 from D3, because they could be entirely
different districts, and what I am trying to count is the number of days that
pass between line items that report events- by district.

Then on the other worksheet tab- Frequencies- I'm trying to find the max
value of column C, by district.

I was definitely more wrong than I originally thought.

"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