# Excel 2007 : IF Statement Help Please

1. ## IF Statement Help Please

Hello all, im new to this forum but im hoping i can get some help to my question!

I am creating a time checking spreadsheet that looks like this:

C D E F G
Reference System Time Difference Average Time Pass / Fail
Time Time +/- Time(s) (middle 3) +/- 0.5 sec
3 11:21:30.00 11:21:29.10 0.90
4 11:21:40.00 11:21:40.90 0.90
5 11:21:50.00 11:21:50.40 0.40 0.72 PASS
6 11:22:00.00 11:22:00.85 0.85
7 11:22:10.00 11:22:09.80 0.20

Columns C and D are formatted as: hh:mm:ss.00
Column E is formatted as: s.00 with the following formulas:
E3 - =ABS(D3-C3)
E4 - =ABS(D4-C4)
E5 - =ABS(D5-C5)
E6 - =ABS(D6-C6)
E7 - =ABS(D7-C7)

Column F is formatted as: s.00 with the following formula in F5:
=TRIMMEAN(E3:E7,2/COUNT(E3:E7))

All of this is working as id like it, e.g. i enter the reference and system times and excel auto populates the difference in mili seconds and then auto calcuates the average of the middle 3 values.

What i would like in column G is this to auto populate either PASS or FAIL depending on the result in column F (average time difference)

I have tried the following formula: =IF(F5>0.5,"FAIL","PASS")

This is so that if the time difference is over 0.5 seconds then it needs to say FAIL but if it is lower than 0.5 seconds then it needs to say PASS.

However this formula isnt working. F5 may show 0.72 seconds and G5 is showing PASS instead of FAIL??

Can anyone tell me where im going wrong?

Is this something to do with the fact that the results in F5 are populated from the TRIMMEAN formula and are not manually inputted?

Hope someone can help me.

Regards
Andy

PS

2. ## Re: IF Statement Help Please

Hi,

It's because you are using time arithmetic where 1 second is 0.0000115741 of a day. i.e. 1 day consists of 24*60*60 seconds. hence you need to be testing for .0000057870

Regards

3. ## Re: IF Statement Help Please

Richard thank you so much!! I cant believe i did not think of this!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1