Solved

Time and Date Formula

Posted on 2012-03-25
8
477 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
  • 4
  • 4
8 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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 14

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

Expert Comment

by:Ingeborg Hawighorst
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 14

Author Comment

by:Edward Pamias
ID: 37763901
No that did not work. I tried the modification it came up blank.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
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 14

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 earned 500 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 14

Author Closing Comment

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now