+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : IF Statement Help Please

  1. #1
    Registered User
    Join Date
    05-02-2012
    Location
    Hull, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    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

    I have attached the spreadsheet.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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. #3
    Registered User
    Join Date
    05-02-2012
    Location
    Hull, England
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: IF Statement Help Please

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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