|
Counting Days between Accidents (Third time lucky?)
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$2:D2, 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(Sh eet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B 46="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 |
Counting Days between Accidents (Third time lucky?)
Replace this formula:
=IF(Sheet3!B18="A","1",IF(Sheet3!B25="A","1",IF(S heet3!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$2:D2, B$2:B2)) Let's change this one to check col C for an entered time: =IF(COUNT(C3),B3-LOOKUP(1E+100,D$2:D2,B$2:B2)) -- Biff Microsoft Excel MVP "aussiegirlone" wrote in message ... 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$2:D2, 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(Sh eet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B 46="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 |
Counting Days between Accidents (Third time lucky?)
Dear T. Valko
This is what happens when I use the formula you just gave Cells D1:D8 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" wrote: Replace this formula: =IF(Sheet3!B18="A","1",IF(Sheet3!B25="A","1",IF(S heet3!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$2:D2, B$2:B2)) Let's change this one to check col C for an entered time: =IF(COUNT(C3),B3-LOOKUP(1E+100,D$2:D2,B$2:B2)) -- Biff Microsoft Excel MVP "aussiegirlone" wrote in message ... 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$2:D2, 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(Sh eet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B 46="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 |
Counting Days between Accidents (Third time lucky?)
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(Sh eet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B 46="A","1"))))) The differences in the formulas a 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$2:D2,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$2:D2,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. -- Biff Microsoft Excel MVP "aussiegirlone" wrote in message ... Dear T. Valko This is what happens when I use the formula you just gave Cells D1:D8 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" wrote: Replace this formula: =IF(Sheet3!B18="A","1",IF(Sheet3!B25="A","1",IF(S heet3!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$2:D2, B$2:B2)) Let's change this one to check col C for an entered time: =IF(COUNT(C3),B3-LOOKUP(1E+100,D$2:D2,B$2:B2)) -- Biff Microsoft Excel MVP "aussiegirlone" wrote in message ... 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$2:D2, 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(Sh eet3!B32="A","1",IF(Sheet3!B39="A","1",IF(Sheet3!B 46="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 |
Counting Days between Accidents (Third time lucky?)
|
Counting Days between Accidents (Third time lucky?)
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. -- Biff Microsoft Excel MVP "Herbert Seidenberg" wrote in message ... Excel 2007 New everything: http://www.mediafire.com/file/zgmjmmqmqym/12_06_08.xlsx |
| All times are GMT +1. The time now is 11:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com