Pass/Fail Formula

[MrsSurg]

Hi,

I am trying to have Column 'U' show pass or fail based on 3 columns showing a time difference.
If columns 'F', 'K' & 'R' are showing a difference greater that 30 minutes I would like column 'U' to show Fail. Anything below 30 minutes to show Pass.
Example in attached image.

Thank you!

 

[6StringJazzer]

Do you mean if the sum of the cells in F K & R are greater/less than 30 minutes?

Also you specify greater than 30 minutes and below 30 minutes. What about exactly 30 minutes?

In Cell U1:

=IF(SUM(F1,K1,R1)>TIME(0,30,0),"Fail","Pass")

(I am baffled as to why the numbers in column F are negative. What are you trying to do?)

 

[Trebor76]

Hi MrsSurg,

Welcome to MrExcel!!

Let me know how this goes (assumes the first row is 2 - change to suit):

=IF(OR(ABS(F2)>0.3,ABS(K2)>0.3,ABS(R2)>0.3),"Fail","Pass")

Regards,

Robert

 

[MrsSurg]

Do you mean if the sum of the cells in F K & R are greater/less than 30 minutes?

Also you specify greater than 30 minutes and below 30 minutes. What about exactly 30 minutes?

In Cell U1:

=IF(SUM(F1,K1,R1)>TIME(0,30,0),"Fail","Pass")

(I am baffled as to why the numbers in column F are negative. What are you trying to do?)

I am actually not sure why column F is showing a negative result and the other two are not. All 3 columns are set up the same (eg, =E9-D9) but just that one column is showing a negative result.

This is a worksheet made by someone in the business previously and has not been working correctly, so I am currently trying to fix it.
We are taking the time from two different sources (timer and datalogger) to performance check that the timers are working correctly. Therefore if the timer is showing more than a 30 minute difference from the datalogger (either 30 mins faster or slower than the datalogger) then that timer will fail its performance check.

It would need to fail if it were 30 minutes plus either faster or slower or pass if 29 minutes or less. Hope that makes sense!

 

[MrsSurg]

Hi MrsSurg,

Welcome to MrExcel!!

Let me know how this goes (assumes the first row is 2 - change to suit):

=IF(OR(ABS(F2)>0.3,ABS(K2)>0.3,ABS(R2)>0.3),"Fail","Pass")

Regards,

Robert

Thank you! That one seems to not work for me.

 

[Trebor76]

That one seems to not work for me.

I take it "not" was a typo? If so you're welcome. If not let us know the formula is not working and we'll try again.

 

[MrsSurg]

I take it "not" was a typo? If so you're welcome. If not let us know the formula is not working and we'll try again.

Sorry, I thought it was, but after playing around to check, it is giving a pass for results over the 30 minute difference, whereas anything over that 30 minutes should be a fail. What am I doing wrong?

 

[Peter_SSs]

Welcome to the MrExcel board!

Do those 'Difference' columns contain formulas? If so, what formula?

Could you give us that sample data with XL2BB so we can easily copy for testing?

 

[MrsSurg]

Welcome to the MrExcel board!

Do those 'Difference' columns contain formulas? If so, what formula?

Could you give us that sample data with XL2BB so we can easily copy for testing?

Unfortunately it looks like it won't let me download XL2BB - I am on a business laptop and they lock things down pretty tightly here.
All 3 'Difference' columns have the two columns in front of them with this formula (but with the corresponding column letters) - =G16-H16

They are set up with custom number of h:mm am/pm and the 'Difference' columns with h:mm

Hope that is helpful!

 

[Peter_SSs]

I have provided my suggestion in column N and if you adapted the post #3 suggestion as shown in column O you could also use that.
The column N formula would more easily scale up if you might end up with more groups of columns.

23 11 07.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
1	Timer	Data	Difference (mins)	Timer	Data	Difference (mins)	Yes/No	Timer	Data	Difference (mins)
2	6:55 AM	6:42 AM	0:13	2:18 PM	2:25 PM	-0:07		6:30 AM	6:28 AM	0:02	Pass	Pass
3	12:45 PM	1:10 PM	-0:25	11:10 PM	11:00 PM	0:10		6:37 PM	6:20 PM	0:17	Pass	Pass
4	8:33 PM	1:09 AM	19:24	7:29 AM	3:14 PM	-7:45		9:57 PM	10:42 PM	-0:45	Fail	Fail
5	6:57 AM	8:26 PM	-13:29	8:35 AM	10:01 AM	-1:26		2:31 PM	12:38 PM	1:53	Fail	Fail
6	10:04 PM	11:12 PM	-1:08	9:53 PM	8:00 AM	13:53		12:42 PM	11:46 AM	0:56	Fail	Fail
Pass Fail

Cell Formulas
Range	Formula
M2:M6,I2:I6,F2:F6	M2	=K2-L2
N2:N6	N2	=IF(MAX(ABS(IF(LEFT(D$1:M$1,2)="Di",D2:M2,0)))>30/1440,"Fail","Pass")
O2:O6	O2	=IF(OR(ABS(F2)>30/1440,ABS(I2)>30/1440,ABS(M2)>30/1440),"Fail","Pass")