Calculating % complete in Excel Part 1

I'm trying to calculate percentage complete, this is one of many percent completes but I will break out my questions to give out the points in fairness.

Step 1:
If column F is "Closed" then on column L = 100%
But if it is "Open" on column F then the calculation should be Column K divided by Column J at column L= 69%

Side Note:  The data  is linked to an access database so it refreshes everyday

See attachment
C--Users-lfreund-Downloads-PERCENT-.xlsx
LUIS FREUNDAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
If column J is for example 100 and column K is 10 then it should be 90%.  Column k is the difference of J.

this?
=IF(F2="Closed",100%,IF(K2=J2, 0%, (J2-K2)/J2))
0
 
Ryan ChongCommented:
in column L, try:

=IF(F2="Closed",100%,K2/J2)

Open in new window


if that column was pulled from database, it would be easier if you can apply that formula into your SQL query.
0
 
LUIS FREUNDAuthor Commented:
Thanks Ryan,

One thing I failed to mention....if the qty orderd is for example 182 and the qty open is 182 then technically that is 0% because it's still open.  If both column J and Column K is the same then it should be 0%

But the formula works great when when its Closed in column F
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Ryan ChongCommented:
if i understand you correctly, try this instead:
=IF(F2="Closed",100%,IF(K2=J2, 0%, K2/J2))

Open in new window

0
 
LUIS FREUNDAuthor Commented:
My deep apologies....it's working great...and my fault for not being concise.  If column J is for example 100 and column K is 10 then it should be 90%.  Column k is the difference of J.
0
 
LUIS FREUNDAuthor Commented:
PERFECT!   Sorry about the confusion.  now off to step 2.  Many Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.