Nested Isnumber formula

[frenchy!]

Hi
Hope you can help.

The following formula works
=IF(Q5="Y","Under Department Review",IF(V5="Y","Under Technical Writer Review",IF(L5="Y","Retired",IF(ISNUMBER(P5),IF(P5<=TODAY()+90,IF(P5<=TODAY(),"Overdue","Expires within 90 days"),"")))))

however, when I try to add another IF(ISNUMBER arguement it errors out.
eg
=IF(Q6="Y","Under Department Review",IF(V6="Y","Under Technical Writer Review",IF(L6="Y","Retired",IF(ISNUMBER(P6),IF(P6<=TODAY()+90,IF(P6<=TODAY(),IF(P6>=TODAY(),"Current","Overdue","Expires within 90 days"),""))))))

Any ideas why it's not working?

And further more, if I can also add an arguement so if there is no date in P6, the entry will remain blank - that would be super.

Appreciate any assistance....
Ta

 

[DonkeyOte]

frenchy!

I don't really follow your 2nd formula in terms of the date parameters... can you elaborate on the window to which "Current" should apply ?

You could adapt your approach to something along the lines of:

=IF(Q5="Y","Under Department Review",IF(V5="Y","Under Technical Writer Review",IF(L5="Y","Retired",IF(ISNUMBER(P5),LOOKUP(P5-TODAY(),{-9.99E+307,0,91},{"Overdue","Expires within 90 days",""}),""))))

and modify both lookup_vector & result_vector of the LOOKUP to incorporate the windows as necessary (in the above prior is "overdue", 0-90 is expires within 90 and anything thereafter returns Null.

 

[frenchy!]

Hi

Thanks for that.

I have to get "current" into it. Basically it would come back current if the date in P6 is earlier than now, and no values are entered in Q6, V6 and L6.

Hope I made sense....

Ta

 

[frenchy!]

And to complicate it further....

I need it to say "In Development" if the cell in P6 (where the date would be entered) is blank.

Is it possible?

 

[DonkeyOte]

I need it to say "In Development" if the cell in P6 (where the date would be entered) is blank.

In which case modify the final null string.

=IF(Q5="Y","Under Department Review",IF(V5="Y","Under Technical Writer Review",IF(L5="Y","Retired",IF(ISNUMBER(P5),LOOKUP(P5-TODAY(),{-9.99E+307,0,91},{"Overdue","Expires within 90 days",""}),"In Development"))))

I have to get "current" into it. Basically it would come back current if the date in P6 is earlier than now...

Still not clear. Perhaps list out the various options with associated result ? e.g:

P < TODAY: Overdue
P <= TODAY + 30: Current
P <= TODAY + 90: Expires Within 90 Days
Else: Null String

 

[frenchy!]

Hi
Sorry for the confusion.

What I mean is:
P < TODAY: Overdue
P > TODAY
P <= TODAY + 90: Expires Within 90 Days
Else: Null String

The only thing that would preceed P>today is 'retired', and the 2 under reviews.

Ta

 

[DonkeyOte]

Sorry, it's Sunday morning so no doubt I am being dense, however, I still don't see from the above where "Current" fits into the grand scheme of things...

Presently you're saying:

-- if P precedes Today then overdue
-- if P is less than or equal to Today + 90 days then "expires within 90"
-- for every other date: Null string

The above is what you have in my prior suggestion. If you need to fit "Current" in you need to outline the window to which current applies in relation to Today.

 

[frenchy!]

It's all good. It's sunday night here...so my brain my be a bit fried.

So - what I need is that cell to generate the status based on the review date.
It will be either Over due (all dates after the review date)
It will be exipring within 90 days (90 days prior to the review date)
Or it will be current (prior to the 90 days in the review date)

Where the null comes into it, is that the review date isn't always completed, in which case it would bring back 'In development'.

Other than my 'current' glitch, the formula works a treat. I haven't used the 'lookup' formula before - so don't understand {-9.99E+307,0,91}

Thanks for all your help - and sorry for the frustration!

 

[frenchy!]

It's all good. It's sunday night here...so my brain my be a bit fried.

So - what I need is that cell to generate the status based on the review date.
It will be either Over due (all dates after the review date)
It will be exipring within 90 days (90 days prior to the review date)
Or it will be current (prior to the 90 days in the review date)

Where the null comes into it, is that the review date isn't always completed, in which case it would bring back 'In development'.

Other than my 'current' glitch, the formula works a treat. I haven't used the 'lookup' formula before - so don't understand {-9.99E+307,0,91}

Thanks for all your help - and sorry for the frustration!

still having trouble with the above issue....

Can anyone suggest a solution?