Complicated nestled IF string

[laurak]

Hi,

I have a complicated nestled If string which isn't working - does anyone know why?

=(IF(OR(((F6-I7)>O3),((F6-I7)<K3)),(IF((F6-I7)=(AB5:AB247)),(((F6-I7)-K7)-1)),((F6-I7)-K7),(IF((F6-I7)=(AB5:AB247)),((F6-I7)-1),(F6-I7))))

Logic:
Find if (F6-I7) is greater than O3 or smaller than K3

Do this if value is true (i.e. (F6-I7) greater than O3 or smaller than K3): if (F6-I7) is equal to a number in the range (AB5:AB247) return (((F6-I7)-K7)-1), and if it's not equal to a number in the range (AB5:AB247) return ((F6-I7)-K7)

Do this if value is false (i.e. (F6-I7) smaller than O3 or greater than K3): if (F6-I7) is equal to a number in the range (AB5:AB247) return ((F6-I7)-1), and if it's not equal to a number in the range (AB5:AB247) return (F6-I7)

 

[laurak]

The formula seems to have cut off above. Here it is in full:

=(IF(OR(((F6-I7)>O3),((F6-I7)<K3)),(IF((F6-I7)=(AB5:AB247)),(((F6-I7)-K7)-1)),((F6-I7)-K7),(IF((F6-I7)=(AB5:AB247)),((F6-I7)-1),(F6-I7))))

Hi,

I have a complicated nestled If string which isn't working - does anyone know why?

=(IF(OR(((F6-I7)>O3),((F6-I7)<k3)),(if((f6-i7)=(ab5:ab247)),(((f6-i7)-k7)-1)),((f6-i7)-k7),(if((f6-i7)=(ab5:ab247)),((f6-i7)-1),(f6-i7))))< em="">

Logic:
Find if (F6-I7) is greater than O3 or smaller than K3

Do this if value is true (i.e. (F6-I7) greater than O3 or smaller than K3): if (F6-I7) is equal to a number in the range (AB5:AB247) return (((F6-I7)-K7)-1), and if it's not equal to a number in the range (AB5:AB247) return ((F6-I7)-K7)

Do this if value is false (i.e. (F6-I7) smaller than O3 or greater than K3): if (F6-I7) is equal to a number in the range (AB5:AB247) return ((F6-I7)-1), and if it's not equal to a number in the range (AB5:AB247) return (F6-I7)

</k3)),(if((f6-i7)=(ab5:ab247)),(((f6-i7)-k7)-1)),((f6-i7)-k7),(if((f6-i7)=(ab5:ab247)),((f6-i7)-1),(f6-i7))))<>

 

[laurak]

It cut off again! Here it is in full:

 

[Marcílio_Lobão]

laurak, Good morning.

Try to use:

=IF(AND(F6-I7>=O3, F6-I7<=K3), IF(ISNUMBER(MATCH(F6-I7, AB5:AB247, 0)), (F6-I7)-K7, (F6-I7)-K7-1), IF(ISNUMBER(MATCH(F6-I7, AB5:AB247, 0)), (F6-I7)-1, (F6-I7)))

Is that what you want?
I hope it helps.

 

[Joe4]

Note: See this regarding the issue with your formula being cut off: http://www.mrexcel.com/forum/about-board/680917-formulas-not-appearing-completely-read.html

 

[laurak]

Hi, that gets rid of the error but for some reason the MATCH and ISNUMBER functions aren't working. Perhaps it's because I'm searching for a date within a range of dates? The formatting matches so I don't know why it wouldn't work.

laurak, Good morning.

Try to use:

=IF(AND(F6-I7>=O3, F6-I7<=K3), IF(ISNUMBER(MATCH(F6-I7, AB5:AB247, 0)), (F6-I7)-K7, (F6-I7)-K7-1), IF(ISNUMBER(MATCH(F6-I7, AB5:AB247, 0)), (F6-I7)-1, (F6-I7)))

Is that what you want?
I hope it helps.

 

[Marcílio_Lobão]

laurak, Good afternoon.

You have a very detailed logic.
First you said about VALUE but now you're saying it's DATE.

To easier things and help us to help you, save your file or an example of your file at a free site, www.sendspace.com and put a link to download here.

Remember to put data there and show us how is the desired result.

 

[laurak]

Hi,

File here: https://www.sendspace.com/file/f0p4s5

What I'm trying to do, is find a formula that I can input into F8, F9, F10, F11 and F12 which will auto-populate the table when a deadline is input into the blue cells.

I need to work backwards from the deadline subtracting a specific number of working hours (see working hours that need to be subtracted in J8:J12) at each stage. There is an issue when I try to exclude non-working hours and holidays/weekends.

By my logic, I thought if the deadline time and date (F7:F12) landed outside the specified working hours I could subtract an additional 16 hours (or whatever was required) to account for the overnight (K8:K12). Then, if the deadline time and date (F7:F12) could be found in the UK hols and weekends column M, an additional day could be subtracted.

Hope this makes sense.
Maybe you will have an less complicated logic!

laurak, Good afternoon.

You have a very detailed logic.
First you said about VALUE but now you're saying it's DATE.

To easier things and help us to help you, save your file or an example of your file at a free site, www.sendspace.com and put a link to download here.

Remember to put data there and show us how is the desired result.