IF Statement with Greater Than value

Orezsce

New Member
Joined
Aug 8, 2018
Messages
1
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:
Rich (BB code):
=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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,296
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

Active Member
Joined
Dec 9, 2015
Messages
324
Its working, which result you want??



<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">DO</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">BH</td><td style="text-align: right;;">2</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">AL</td><td style="text-align: right;;">3</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">24</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M6</th><td style="text-align:left">=IF(<font color="Blue">K6="DO",0,IF(<font color="Red">K6="BH",8,IF(<font color="Green">K6="AL",8,IF(<font color="Purple">L6-K6>6,-1,(<font color="Teal">L6-K6</font>)*24</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M7</th><td style="text-align:left">=IF(<font color="Blue">K7="DO",0,IF(<font color="Red">K7="BH",8,IF(<font color="Green">K7="AL",8,IF(<font color="Purple">L7-K7>6,-1,(<font color="Teal">L7-K7</font>)*24</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M8</th><td style="text-align:left">=IF(<font color="Blue">K8="DO",0,IF(<font color="Red">K8="BH",8,IF(<font color="Green">K8="AL",8,IF(<font color="Purple">L8-K8>6,-1,(<font color="Teal">L8-K8</font>)*24</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">M9</th><td style="text-align:left">=IF(<font color="Blue">K9="DO",0,IF(<font color="Red">K9="BH",8,IF(<font color="Green">K9="AL",8,IF(<font color="Purple">L9-K9>6,-1,(<font color="Teal">L9-K9</font>)*24</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,136,322
Messages
5,675,075
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top