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.
Bookmarks