Determining Percentage across cells

[Taiter]

Greetings, First let me thank you in advance with any help and/or suggestions on where I can start to figure out a solution. I have a project funds tracking sheet and am trying to determine the percentage of how much is over obligated based on estimates received and then had funds de-obligated and/or balanced out later in the process. The formula currently using is "=Difference/Max Funded" and shown as a percentage. But get the #DIV/0! For those that were cancelled and no funds were obligated (but I still have to track them as part of the greater project); but do have a column that show the status of Cancelled. Is it possible to have a formula that will show the overage as 0% if the status is "Cancelled" and if any other status it applies the formula above?

Again thank you for any help.

 

[SullyWYO]

You just need an IF-THEN statement.

The structure is =IF(logic test, value if true, value if false). In your case, use:

=IF([@Status] = "Cancelled", 0,([@Difference]/[@[Max Funded]]))

The formula above works if the data is in a table. If it is not in a table, change the [Column References] to the appropriate cells.

 

[Taiter]

Thank you. I had an idea it was an IF formula but was hung up on using the text. This is going to be big help.