# Need help to simplify a nested formula

#### catherinemoore

##### New Member
I need help with the formula in F4. In row 4 the working hours are entered for the day. In row 5, any callout hours are added. The aim is to show "N" in F4 if there is less than a 12 hour gap between each finish time and the next start time, or show "Y" if there are 12 hours or more.

I need to include the following in the formula to get an "N" result:
(hrs between D4 & C5)>12
(hrs between D4 & G4)>12
(hrs between D4 & G5)>12
(hrs between D5 & G4)>12
(hrs between D5 & G5)>12
(hrs between G4 & G5)>12

So far I have included the first two criteria in the formula, but it's already pretty lengthy, so if anyone has any ideas on how to simplify it, I would really appreciate it!
HOURS.xls
CDEFGHIJ
2SUNDAYRESTMONDAYREST
3STARTFINISHHRS13/14STARTFINISHHRS13/14
408:0016:008.0N08:0016:008.0
522:0023:001.00.0
Worked Hrs

Thanks.

Catherine

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### West Man

##### Well-known Member
Quick and untested but try =IF(MAX(C5-D4,1-D4+G4,1-D4+G5,1-D5+G4,1-D5+G5,G5-G4)>0.5,"N","Y")

This may at least point you in the right direction.

#### catherinemoore

##### New Member
I can see where you are coming from, but it doesn't quite work. I like the MAX idea, but still can't get the calcs inside the MAX function to work. Does anyone have any ideas?

#### West Man

##### Well-known Member
After posting, I did a test on your sample data and got the proper result. Under what circumstances does the formula fail?

#### JamieDuncan

##### Board Regular

=IF((D4+C5)>12,(IF((D4+G4)>12,(IF((D4+G5)>12,(IF((D5+G4)>12,(IF((D5+G5)>12,(IF((G4+G5)>12,"N","Y")),"Y")),"Y")),"Y")),"Y")),"Y")

this ones a little bit shorter,

#### catherinemoore

##### New Member
West Man

Sorry - I got a little confused. I actually need to know if the smallest interval is less than 12 hrs, so the formula would now look like this:

=IF(MIN(C5-D4,1-D4+G4,1-D4+G5,1-D5+G4,1-D5+G5,G5-H4)<0.5,"N","Y")

The problem is that when some of the cells are blank, the calculation produces a negative number, which then ends up being the minimum and affects the result. How can I get the formula to ignore the calculations which involve a blank cell (e.g. if C5-D4 produced a negative number, then it would be ignored in the calculation of the minimum)?

Thanks

Catherine

#### West Man

##### Well-known Member

I am bumping this in hope that others can assist you. I got hit with an emergency that will ty me up for a couple days. Sorry I can not help further at this time.

#### West Man

##### Well-known Member
Untested but try:

=IF(MIN(IF(C5-D4<0,9999999,C5-D49),IF(1-D4+G4<0,9999999,1-D4+G4),IF(1-D4+G5<0,9999999,1-D4+G5),IF(1-D5+G4<0,9999999,1-D5+G4),IF(1-D5+G5<0,9999999,1-D5+G5),IF(G5-H4<0,9999999,G5-H4))<0.5,"N","Y")

I'm sure a shoter array formula is possible.

#### catherinemoore

##### New Member
No, it doesn't work - it gives the result "N" whatever you put in. Any other ideas?

Thanks

Catherine

#### catherinemoore

##### New Member
Can anyone help? I'm still stuck on this!

Thanks!

Replies
1
Views
185
Replies
8
Views
584
Replies
2
Views
137
Replies
3
Views
242
Replies
0
Views
143

1,141,011
Messages
5,703,725
Members
421,311
Latest member
tanujath

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

### Which adblocker are you using?

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

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