Solved

Time and Date Formula

Posted on 2012-03-25
8
489 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 14

Author Comment

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

773 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