Incorrect returns from If statement (Identifying Gaps in Time)

[ag4431]

I am brand new to the forum so Hello to all. I am self taught and have come across a problem I cannot seem to understand. I am gathering data from a device that records vital signs. It records the time, a persons heart rate, and a persons oxygen saturation every five seconds. However, the device sometimes has an error and it will not record any vital signs and essentially skip that time. In the numerous hours the device is activated it is very difficult to pick out the subtle gaps in time visually. I have tried to create a formula that will assist me with this.

I first tried =If(B3=(B2+TIME(00,00,05))), "good", "PROBLEM"

But some cells would return "PROBLEM" even if they were 5 seconds after the time before.

I then tried to rephrase my formula to =If(B3-TIME (00,00,05)=B2), "good" "PROBLEM"

Hoping that maybe turning the formula around would correct the problem somehow, but different cells that were supposed to return "good" returned "PROBLEM" again.

Both formulas did find true "PROBLEM" cells but also included these seemingly random cells. I tried combining both formulas in with the AND function but still the problem persisted. I was wondering if anyone would be able to help me identify the problem or suggest a different formula that might help me.

In Cell Q3 the formula works properly but in cells Q10 and Q18 the formula returns "PROBLEM"
FYI I made cell R3 Just to show the formula that appears in Q3
Thank you

[AliGW]

Try this:

=IF(B3-TIME(0,0,5)=B2,"Good","Problem")

[ag4431]

Thank you for the quick response! I tried that formula but unfortunately it still makes the same mistake and leaves cells with the wrong result. Any other ideas?

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[ag4431]

Here is a mock workbook. The first column show the data as it is in my project. The second column of much fewer rows is my desired result. I would like the good to appear for all cells that are 5 seconds after the cell above them and problem to appear for cells that are not 5 seconds after the cell above them indicating that that recording has been skipped and I will need to correct it.

[AliGW]

Try this:

=IF(ROUNDDOWN((B3-B2),5)=0.00005, "good","PROBLEM")

I think this is to do with the floating point issue in Excel.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Jonmo1]

The problem is that time is much more precise than to the second.
There is a variance of somewhere around 0.0000115740258479491 in 1 second.

i.e.
0.4777141204 formatted as hh:mm:ss will be 11:27:55
as will
0.4777256944 formatted as hh:mm:ss will also be 11:27:55


When doing a If TIME=TIME comparison, we definately need to round to the second..

I would do it like this

=If(TEXT(B3,"hh:mm:ss")=TEXT(B2+TIME(00,00,05),"hh:mm:ss"),"Good","Bad")

[ag4431]

Thank you both for the help I used Jonmo1's formula and it worked perfectly

[AliGW]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.