If overide formulae

J

jonespandrew

New member
Joined
Jan 27, 2016
Messages
122
Reaction score
0
Points
0
Can onyne help with this.

In english i am trying to get it to do the following

=If(K2=""."Risk",If(K2<=J2,"Pass,",If(K2>J2,"Fail", Overide if L2="INC","TBA"
 
=IF(L2="INC","TBA",IF(K2="","Risk",IF(K2<=J2,"Pass",IF(K2>J2,"Fail"))))
?
 
Thank you.

Now another problem

I have the following coloums

Resolved Date Resolved Time Target Resolved Date Target Resolved time.


I am trying to get the fourmulate to do the followin
if the resolved date and resolved time > target resolve date target resolve time then fail. is this possible.

Reason for splitting the fields is so that the pivot table that is created from this will let me select one date at a time etc.

This is what have so far but i get a # value # instead of what i need

=IF(N2="","TBA",IF(L2:M2="","Risk",IF(L2:M2<=O2:p2,"Pass",IF(L2:M2>O2:p2,"Fail"))))
 
jonespandrew said:
I have the following coloums

Resolved Date Resolved Time Target Resolved Date Target Resolved time.
Click to expand...
It's really clear how those columns are headed.





jonespandrew said:
I am trying to get the fourmulate to do the followin
if the resolved date and resolved time > target resolve date target resolve time then fail. is this possible.

Reason for splitting the fields is so that the pivot table that is created from this will let me select one date at a time etc.

This is what have so far but i get a # value # instead of what i need

=IF(N2="","TBA",IF(L2:M2="","Risk",IF(L2:M2<=O2:p2,"Pass",IF(L2:M2>O2:p2,"Fail"))))
Click to expand...
It will surprise you to know:
a) I can't see your screen over your shoulder.
b) I can't read minds.

So how am I supposed to know which columns are which?

Do the right and sensible thing: supply a file - an Excel file, not a picture of one.
The effort you're prepared to go to to get this solved will be reflected in the effort people will make to solve it for you. No effort made means no effort returned.
 
jonespandrew said:
i get a # value # instead of what i need
Click to expand...

I agree with everything posted by P45cal, so I am not at this stage able to offer you a solution, but to answer your question, you cannot include arrays of cells in an IF() function in the way your doing.
It must be done as an array formula that includes other functions if your wanting to resolve the returned value as one cell entry. If its not an array formula, it will return #VALUE!. If its an array formula without the "other functions" support, it will return a TRUE or FALSE based on the first element only in each array of cells.
 
Last edited:
=IF(N2="","TBA",IF(COUNTBLANK(L2:M2)>0,"Risk",IF((L2+M2)>(O2+P2),"Fail","Pass")))
 
jonespandrew said:
Please see attached
Click to expand...

A perfect illustration of how much easier it is to provide a working solution when we can see your data in situ in a worksheet, together with your (attempted) formula. We can correct errors in your formula alone, but we couldn't pick up that even as an array formula, it wasn't handling the times correctly, which P45cal has fixed for you in post #7. :)
 
Thank you.

I am trying also to get the following to work

If t2 and u2=Risk then display risk, If T2:u2 = Pass display pass, if T2:U2 =Fail Display Fail If T2=Pass and U2 =Fail, Fail, If U2=Pass and t2 =fail display fail
 
Could you tell us what the results should be where I have question marks in the picture below, or if they can never be that way, say so. Also, can there be anything else in columns T and U?
2017-04-26_094634.jpg
 
If T and U are pass the Result is Pass
If T and U are Fail Result = Fail
If T and U are risk = Result Risk
If T is Fail and U Is Pass = Result would be Fail
If T is Pass and U is Fail Result would be Fail
 
jonespandrew said:
If T and U are pass the Result is Pass
If T and U are Fail Result = Fail
If T and U are risk = Result Risk
If T is Fail and U Is Pass = Result would be Fail
If T is Pass and U is Fail Result would be Fail
Click to expand...
Why do I chuffing bother?
 
Sorry I have read you last response wrong Write

Fail Risk would be a fail
Risk pass would be a Risk
Risk Fail would be a fail
Pass Risk would be risk
 
Array-enter (Ctrl+Shift+Enter, not just Enter) and copy down:
=INDEX({"Fail";"Risk";"Pass"},MIN(MATCH(T2:U2,{"Fail","Risk","Pass"},0)))
 
…and 10 days down the line still can't be bothered to say thankyou.
 
Last edited:
jonespandrew said:
Sorry been busy at work

Thank you much apreciated
Click to expand...

You've made this excuse before. If you had the time to come back and get the information you asked for, then you had the time to post two simple words of gratitude. Remember that we are ALL busy people, and those who help here do so voluntarily and for no recompense. The least you can do is thank someone when they help you - it's just common courtesy.
 
You must log in or register to reply here.
Back
Top