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
8
Medium Priority
?
516 Views
Last Modified: 2012-03-25
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
Comment
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
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 50
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

by:Edward Pamias
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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 18

Author Comment

by:Edward Pamias
ID: 37763901
No that did not work. I tried the modification it came up blank.
0
 
LVL 50
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

by:Edward Pamias
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

by:
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

by:Edward Pamias
ID: 37763960
Thank you. This will work.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question