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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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?
 
Upvote 0
After posting, I did a test on your sample data and got the proper result. Under what circumstances does the formula fail?
 
Upvote 0
=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:
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

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
Back
Top