?
Solved

Time and Date Formula

Posted on 2012-03-25
8
Medium Priority
?
523 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
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 19

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 19

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 19

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 19

Author Closing Comment

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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

In this article, I will demonstrate that how to do a PST migration from Exchange Server to Office 365. This method allows importing one single PST, or multiple PST's at once.
This tutorial is about creating a new Microsoft Online User Profile account along with how to transfer your files and settings. You may be faced with this situation if your existing user profile has become corrupted.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

592 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