Using multiple functions in a formula

MelG

Hi, 

I am using a formula to determine "Planned % Complete" based on start date/end date and todays date. The formula I am using is;

=(MIN(TODAY(), Finish1) - Start1 + 1) / (Finish1 - Start1 + 1)

I want to nest the following formula so that if the start date is AFTER todays date, the "Planned % Complete" will be zero; 

=IF(Start1 <= TODAY(), 0, 0)

Now, I would also like to add an element that removes weekends between the start and end date using the WORKDAY function.

Any suggestions how I can use all three functions in the same cell? Or an easier way to calculate the "Planned % Complete" obtaining the same results as mentioned above?

Regards,

Mel

CanadaJim

Hi Mel,

This is actually pretty straightforward.  You can, in general, put functions within functions as long as the output data type is correct.  For example, so long as the output is a number, you can do math with it.

For your specific case, I would start with the IF statement to address tasks that haven't started.

Then I would do the logic on the remaining tasks.  For example:

IF(Start1 <= TODAY(), 0, (MIN(TODAY(), Finish1) - Start1 + 1) / (Finish1 - Start1 + 1))

The third component of the IF statement is processed if the first condition is FALSE.

In other words, if the tasks hasn't started yet, you'll get a zero.  Otherwise, you'll get the calculation.

Just one warning: If your task is 1 day long, you will get an error due to Divide By Zero in your formula.  Let us know if you need help with that, but a hint is to put an if statement in front of your calculation, inside the FALSE component of the first IF statement.  This would be called a "Nested IF".

Jim