Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a formula by percentage within a formula? | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
formula percentage problem | New Users to Excel | |||
How do I formula a percentage with text values in the data field | Excel Worksheet Functions | |||
Percentage Formula | Excel Worksheet Functions |