Counting Days between Accidents (Third time lucky?)

[aussiegirlone]

I have asked this question a couple of times and each time the help I have
received has been extremely excellent. Services that are well and truly
appreciated! And a Thank you up-front for any help that could be given.

Now for my problem and I hope my Data Sheet as shown below will help

This formula works well in column E
=IF (D3="", FALSE, B3-LOOKUP (1E+100, D$22, B$2:B2))

However, the formula above only works on column D if the cells are not
referenced to other Cells or Sheets! The data in column D must be inputted
manually for the formula above to work.
It does not work if column D already has a formula in it as shown below.
=IF(Sheet3!B18="A","1",IF(Sheet3!B25="A","1",IF(Sheet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B46="A","1")))))

Column E is how it should look like but I get #N/A because of the formula in
column D
I have spent quite some time trying to get this to work for me but to no
avail. Can anyone help make column E perform as the way you see it. please
please please????

B C D E
Date Time of Accident Number of Accidents Days Since last Accident
1/1/2008 12:30:00 PM 1 0
5/1/2008 FALSE 4
6/1/2008 2:30:00 PM 1 0
7/1/2008 3:30:00 PM 1 0
8/1/2008 FALSE 1
10/1/2008 5:30:00 PM 1 0
12/1/2008 6:30:00 PM 1 2

 

[T. Valko]

Replace this formula:

=IF(Sheet3!B18="A","1",IF(Sheet3!B25="A","1",IF(Sheet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B46="A","1")))))

Use this one:

=IF(OR(Sheet3!B18="A",Sheet3!B25="A",Sheet3!B32="A",Sheet3!B39="A",Sheet3!B46="A"),1,"")

When you quote a number: "1", Excel evaluates it as TEXT and not a numeric
number.

=IF(D3="", FALSE, B3-LOOKUP (1E+100, D$22, B$2:B2))

Let's change this one to check col C for an entered time:

=IF(COUNT(C3),B3-LOOKUP(1E+100,D$22,B$2:B2))

 

[aussiegirlone]

Dear T. Valko
This is what happens when I use the formula you just gave
Cells D18 are all empty until the data is entered from sheet three

=IF(OR(Sheet3!B18="A",Sheet3!B25="A",Sheet3!B32="A",Sheet3!B39="A",Sheet3!B46="A"),1,"")

However, I noticed that cells D1 & D2 must have data in them for cell E2 to
produce an amount. If I only enter data into D2, then E4 returns false. But
when I enter data into D3 also, then E4 will return 4.
E3 receives no amount until I put data into D4

B D E
1 Date of Accident Number of Accidents
2 1/1/2008 1 4
3 5/1/2008 1 FALSE
4 6/1/2008 FALSE
5 7/1/2008 FALSE
6 8/1/2008 FALSE
7 10/1/2008 FALSE
8 12/1/2008 FALSE

 

[T. Valko]

Ok, I'm "officially" confused!

This formula:

=IF(OR(Sheet3!B18="A",Sheet3!B25="A",Sheet3!B32="A",Sheet3!B39="A",Sheet3!B46="A"),1,"")

Does almost the exact same thing as this formula:

=IF(Sheet3!B18="A","1",IF(Sheet3!B25="A","1",IF(Sheet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B46="A","1")))))

The differences in the formulas are: the first formula returns the *number*
1 where your formula returned the *TEXT* value 1. 1 and "1" are not the
same! Another difference is the first formula returns a blank when no cells
on sheet3 =A. Your formula returns FALSE when no cells on sheet3 =A. What
result do you want when no cells on sheet3 =A?

The formula to calculate the number of days between accidents is based on
some kind of entry that shows an accident actually happened. I thought it
would be a good idea to base this on the "Number of Accidents" column. You
could also base this on the "Time of Accident" column. Which of those
columns do you want to base the formula on?

The formula to calulate the days between accidents works properly and
returns the correct result. Since the "Number of Accidents" column will only
show 1 (based on the formula in that column) when an accident is logged:

Entered in E3:

=IF(D3=1,B3-LOOKUP(1E+100,D$22,B$2:B2),0)

This will return 0 when column D does not =1.

Or, if you want to base the formula on the "Time of Accident" column:

=IF(COUNT(C3),B3-LOOKUP(1E+100,D$22,B$2:B2),0)

Remeber, since there's no previous data to compare the first entry to you
have to manually enter the result you want in cell E2.

 

[Herbert Seidenberg]

Excel 2007
New everything:
http://www.mediafire.com/file/zgmjmmqmqym/12_06_08.xlsx

 

[T. Valko]

I don't think that's what they want.

They want to calculate the number of days since the *last* accident.

Just an observation: you're limiting your audience by doing everything in
Excel 2007.