Solved

Time and Date Formula

Posted on 2012-03-25
8
496 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 16

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 16

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 16

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 16

Author Closing Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

791 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