IF Statement with Greater Than value

[Orezsce]

Hi All

I have a rota on Excel that originally referenced a specific cell to determine whether or not to include a lunch break in the total hours cell. I would prefer to simply add this into the IF statement and eradicate the unnecessary extra cell where a lunch break is manually selected.

Currently, I have this, but it does not correctly deduct the hour:

=IF(K6=ʺDOʺ,0,IF(K6=ʺBHʺ,8,IF(K6=ʺALʺ,8,IF(L6-K6>6,-1,(L6-K6)*24))))

I can't reference the total cell (N6) without creating a circular reference, which was my only other thought.

Any ideas?

 

[jorismoerings]

Hi,

Your formula will never reach the calculation at the end if K6 hold one of the criteria you've mentioned beside K6 holds criteria so basically the calculation part says =L6-"AL">1

Just show soem sample data and explain more indepth what you expect.

 

[Muhammad_Usman]

Its working, which result you want??

Book1
	K	L	M
6	DO	1	0
7	BH	2	8
8	AL	3	8
9	4	5	24
Sheet4

Cell Formulas
Range	Formula
M6	=IF(K6="DO",0,IF(K6="BH",8,IF(K6="AL",8,IF(L6-K6>6,-1,(L6-K6)*24))))
M7	=IF(K7="DO",0,IF(K7="BH",8,IF(K7="AL",8,IF(L7-K7>6,-1,(L7-K7)*24))))
M8	=IF(K8="DO",0,IF(K8="BH",8,IF(K8="AL",8,IF(L8-K8>6,-1,(L8-K8)*24))))
M9	=IF(K9="DO",0,IF(K9="BH",8,IF(K9="AL",8,IF(L9-K9>6,-1,(L9-K9)*24))))