Formula w/negative no.s & Percentage
 

Let's say I've created column A(# DAYS BETWEEN ASSIGNED & DUE DATE) and B (#
DAYS BETWEEN TODAY & DUE DATE) Column C calculates number of days between
today's date and Due Date. Column D uses info in Column C to calculate what
percentage of completion the deliverable should be at. When a deliverable
is on track, Column C will have a positve number using this formula
(=SUM(1-(K2/J2)) When Column C is a negative number representing something
overdue, the formula above does not work. I need a formula in essence to
show a negative percentage (e.g. - 25%) when the deliverable is overdue using
info from Columns, A, B, and C above as stated. Can anybody help please!

Formula w/negative no.s & Percentage
 

Anshin --

Here's an approach:
A B C D
Assigned Today Due Target
1-Mar-06 = today() 25-Apr-06 Formula
1-Mar-06 = today() 1-Apr-06

Here's one formula:
=IF((B2-A2)/(C2-A2)<1,(B2-A2)/(C2-A2),-(B2-A2)/(C2-A2)+1)
Here's another:
=IF((B2-A2)/(C2-A2)<1,(B2-A2)/(C2-A2),"Overdue!")

Hope that helps.


"Anshin" wrote:

Let's say I've created column A(# DAYS BETWEEN ASSIGNED & DUE DATE) and B (#
DAYS BETWEEN TODAY & DUE DATE) Column C calculates number of days between
today's date and Due Date. Column D uses info in Column C to calculate what
percentage of completion the deliverable should be at. When a deliverable
is on track, Column C will have a positve number using this formula
(=SUM(1-(K2/J2)) When Column C is a negative number representing something
overdue, the formula above does not work. I need a formula in essence to
show a negative percentage (e.g. - 25%) when the deliverable is overdue using
info from Columns, A, B, and C above as stated. Can anybody help please!

Formula w/negative no.s & Percentage
 

Thank you, thank you!! You saved my life!! This worked perfectly.

Anshing.

"pdberger" wrote:

Anshin --

Here's an approach:
A B C D
Assigned Today Due Target
1-Mar-06 = today() 25-Apr-06 Formula
1-Mar-06 = today() 1-Apr-06

Here's one formula:
=IF((B2-A2)/(C2-A2)<1,(B2-A2)/(C2-A2),-(B2-A2)/(C2-A2)+1)
Here's another:
=IF((B2-A2)/(C2-A2)<1,(B2-A2)/(C2-A2),"Overdue!")

Hope that helps.


"Anshin" wrote:

Let's say I've created column A(# DAYS BETWEEN ASSIGNED & DUE DATE) and B (#
DAYS BETWEEN TODAY & DUE DATE) Column C calculates number of days between
today's date and Due Date. Column D uses info in Column C to calculate what
percentage of completion the deliverable should be at. When a deliverable
is on track, Column C will have a positve number using this formula
(=SUM(1-(K2/J2)) When Column C is a negative number representing something
overdue, the formula above does not work. I need a formula in essence to
show a negative percentage (e.g. - 25%) when the deliverable is overdue using
info from Columns, A, B, and C above as stated. Can anybody help please!