Need help to simplify a nested formula

catherinemoore

New Member
Joined
Oct 10, 2006
Messages
10
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
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Oct 10, 2006
Messages
10
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
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Aug 23, 2006
Messages
132

ADVERTISEMENT

=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, :rolleyes:
 

catherinemoore

New Member
Joined
Oct 10, 2006
Messages
10
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
Joined
Mar 27, 2006
Messages
1,175

ADVERTISEMENT

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
Joined
Mar 27, 2006
Messages
1,175
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
Joined
Oct 10, 2006
Messages
10
No, it doesn't work - it gives the result "N" whatever you put in. Any other ideas?

Thanks

Catherine
 

Forum statistics

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