# Time and Date Formula

I have a simple add (for you experts) that I need to this formula. See below

=IF(D2<>"",IF(ISNA(VLOOKUP(A2,\$J\$2:\$K\$5,1, FALSE)),"",IF(D2<VLOOKUP(A2,\$J\$2:\$K\$5,2),IF((1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2)<0.5,"< 50%",IF((1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2)<0.75,"< 70%",IF((1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2)<1,"< 100%",""))),"")),"")

In addition to the above I need something to give me a Fail for anything over 100% and inserted to the current formula. The reason being I need the % and the fail to be in the same column so I can pivot this out correctly. Your assistance is appreciated!
LVL 19
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft MVP ExcelCommented:
Hello,

how about just changing the false statement for the last nested IF? You should have covered any other possibilities by now, so everything that has not already been ruled out must be greater than 100%

=IF(D2<>"",IF(ISNA(VLOOKUP(A2,\$J\$2:\$K\$5,1, FALSE)),"",IF(D2<VLOOKUP(A2,\$J\$2:\$K\$5,2),IF((1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2)<0.5,"< 50%",IF((1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2)<0.75,"< 70%",IF((1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2)<1,"< 100%","FAIL"))),"")),"")

cheers, teylyn
The formula worked well as it was I just needed something added to give me a "Fail" if the % was greater than 100% For example <.05 would give me < 50% so I would want >1 to give me "Fail".
Microsoft MVP ExcelCommented:
Does that mean the question is answered or do you still have a problem? I can't quite tell. If the question is answered, please accept the above post as the solution.

cheers, teylyn
No that did not work. I tried the modification it came up blank.
Microsoft MVP ExcelCommented:
It's hard to tell what you want to achieve.

Can you post the data in J2:K5 and the value in D2?
here is the sample file in the % column I want anything that is over 100% to say failed.We have a certain time frame to complete work that is listed in the table to the right. Anything over those times is failed. Anything under that I wanted the % listed.
sample-sheet.xlsx
Microsoft MVP ExcelCommented:
You need to remove the part

D2<VLOOKUP(A2,\$J\$2:\$K\$5,2)

because for any value that would result in more than 1 would require D2 to be smaller than the looked up value.

Try this approach with Lookup instead of nested IF()

=IF(D2<>"",IF(ISNA(MATCH(A2,\$J\$2:\$J\$5,0)),"",LOOKUP(1-(VLOOKUP(A2,\$J\$2:\$K\$5,2)-D2)/D2,{0,0.5,0.75,1},{"< 50%","< 75%","< 100%","Fail"})),"")

Start in E2 and copy down

cheers, teylyn

Experts Exchange Solution brought to you by