Calculated column based on another column not working

Question

Hi,

I am struggling to get my formula to work. I have created a calculated column which I want to show text based off another column called "% Complete" which is set to column type: number %.

When someone manually enters how much % they have completed of that tasks in the "% complete" column, I want my calculated column to show the following:

if % complete is blank show "Not Started"

if % complete is 0% show "Not Started"

if % complete is more than 0% show "In Progress"

if % complete is 100% show "Completed"

This is my current formula, which isn't working.

=IF([%Complete]>99,"Completed",IF([%Complete]>0,"In Progress", IF([%Complete]=0,"Not Started",IF(ISBLANK([% Complete],"Not Started"))))

thanks in advance :)

Answer 1

Try making the 0,99 and 100 text: "99", "0" etc

.

Here are some articles about creating nested IFs

. IF function – nested formulas and avoiding pitfalls https://support.microsoft.com/en-us/office/if-function-nested-formulas-and-avoiding-pitfalls-0b22ff44-f149-44ba-aeb5-4ef99da241c8

Excel for Microsoft 365 Excel for Microsoft 365 for Mac More...

The IF function allows you to make a logical comparison between a value and what you expect by testing for a condition and returning a result if True or False.

• =IF(Something is True, then do something, otherwise do something else)

So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.

IF statements are incredibly robust, and form the basis of many spreadsheet models, but they are also the root cause of many spreadsheet issues. Ideally, an IF statement should apply to minimal conditions, such as Male/Female, Yes/No/Maybe, to name a few, but sometimes you might need to evaluate more complex scenarios that require nesting* more than 3 IF functions together.

* “Nesting” refers to the practice of joining multiple functions together in one formula.

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false.

.

Nested IF function example

https://exceljet.net/formula/nested-if-function-example

Generic formula =IF(T1,R1,IF(T2,R2,IF(T3,R3,IF(T4,R4,R5)))) Summary

The IF function can be nested inside of itself to handle multiple conditions. In the example shown, a nested IF formula is used to assign a grade to a score. The formula in D5 contains 5 separate IF functions: =IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A")))) Explanation

This article describes the Excel nested IF construction. Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests.

. IF and Nested IF Statements in Excel using Function Builder https://office-watch.com/2022/if-nested-if-statements-excel/

by Megan Casey      27 May 2022

An IF statement might sound like a complicated thing that programmers do, but it’s actually fairly easy to do in Excel. An IF statement basically puts something into a cell depending on certain conditions – if A is true,  put X in this cell. You can even use what’s called a nested IF statement if you have multiple conditions that you want to meet.  

To start with a simple example, let’s say we have a spreadsheet where we enter our expenses, but any expense over $100 needs to have an authorization number, so we want a note next to those expenses to remind us that we need to enter that number.