Excel, Nested IF Nightmare!

[Ryath]

Hey all,

Need help with the dreaded Nested if problem as it's driving me nuts!

The scenario:-

17 Columns (16 interval dates with 15 month gaps and 1 reference or dead date)

Many rows of varying start interval dates and dead dates.

To Find the closest match using the dead date to the 16 interval dates with a 90 day buffer ie. dead date - interval > 90 otherwise chooses interval date before.

I already know about the 7 function limit in excel and before I only had 12 interval dates to calculate, so was able to split into 2 columns and using the max nested limit per column and choosing the earliest of the 2.

Now the boss has given me more dates it's driving me crazy as the sheet is turning into a huge nightmare!

This is the formula I've been using for the dates:-

IF((Dead-Int1)>90,Int1,IF((Dead-Int2)>90,Int2,IF((Dead-Int3)>90,Int3... ,0)))))

Obviously I can't do this 16 times due to excel limits... and I really don't want to split it into 3 columns either, any ideas?

Tried HLOOKUP, but that function doesn't take my 90 day buffer into account and I wouldn't know how to tell that function to reference "Next date" before or column before.

Cheers!!


Will

 

[macropod]

Hi Will,

How about something along the lines of:
=MIN(((Dead-Int1)>90)*Int1,((Dead-Int2)>90)*Int2,((Dead-Int3)>90)*Int3...)

Cheers

[MS MVP - Word]

 

[macropod]

Hi Will,

I've been thinking about this some more and realised the solution I posted probably won't work the way you want.

A lookup might still be possible, though. For example something along the lines of:
=LOOKUP(Dead-90,IntVals)

Cheers

[MS MVP - Word]

 

[Ryath]

Hey!

Thanks for your suggestions!
The first one didn't work as you mentioned but I'm trying the LOOKUP at the moment (never even thought of using that even though I tried HLOOKUP) and so far (first few values out of ~800) it seems to work!
Will do abit more testing and will get back!

Ta!

Will

Will

 

[Ryath]

It works!
Cheers macropod! D

Will