Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Time and Date Formula

Posted on 2012-03-25
Medium Priority
516 Views
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!
0
Question by:Edward Pamias
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4

LVL 50

Expert Comment

ID: 37763790
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
0

LVL 18

Author Comment

ID: 37763891
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".
0

LVL 50

Expert Comment

ID: 37763899
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
0

LVL 18

Author Comment

ID: 37763901
No that did not work. I tried the modification it came up blank.
0

LVL 50

Expert Comment

ID: 37763914
It's hard to tell what you want to achieve.

Can you post the data in J2:K5 and the value in D2?
0

LVL 18

Author Comment

ID: 37763925
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
0

LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 37763941
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
0

LVL 18

Author Closing Comment

ID: 37763960
Thank you. This will work.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me â€¦
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.