# 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
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Business Systems Analyst , ex-Senior Application EngineerCommented:
in column L, try:

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

if that column was pulled from database, it would be easier if you can apply that formula into your SQL query.
0
Author 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
Business Systems Analyst , ex-Senior Application EngineerCommented:
if i understand you correctly, try this instead:
=IF(F2="Closed",100%,IF(K2=J2, 0%, K2/J2))
0
Author 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
Business Systems Analyst , ex-Senior Application EngineerCommented:
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

Experts Exchange Solution brought to you by