IF Statement with Greater Than value


New Member
Aug 8, 2018
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):

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


Well-known Member
Jul 4, 2014

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.


Active Member
Dec 9, 2015
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

Latest member

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