Hi - I need help with this formula. I am getting UNPARSEABLE error with it each time

Suumit Yadav

The formula is =IF([Percent Complete]@row = 1, "GREEN"), IF(AND([Percent Complete]@row >=0.80, ([Percent Complete]@row <1), "Yellow", "Red")).

What I am trying to get.

If Percentage is 100. RAG column should say Green. If Percentage is greater than 40 it should say Yellow and if it is less than 80 than it should Red.

David Tutwiler

Very close Suumit. I think the only issue in what you have is that you need to make the next nested IF statement the FALSE part of your first if statement instead of a new statement entirely. So it would look more like this:

=IF([Percent Complete]@row = 1, "GREEN", IF(AND([Percent Complete]@row >=0.80, [Percent Complete]@row < 1), "Yellow", "Red"))

David Tutwiler

Very close Suumit. I think the only issue in what you have is that you need to make the next nested IF statement the FALSE part of your first if statement instead of a new statement entirely. So it would look more like this:

=IF([Percent Complete]@row = 1, "GREEN", IF(AND([Percent Complete]@row >=0.80, [Percent Complete]@row < 1), "Yellow", "Red"))

Brent Wilson

There are some issues with the logic..

If Percentage is greater than 40 it should say Yellow and if it is less than 80 than it should Red.

those two statements conflict. Example 60 is both greater than 40 and less than 80. So is it red or yellow?

Assuming you mean 100% -> Green

100-80 -> Yellow

79-40 -> Red

40 and lower -> Empty

=IF([Percent Complete]@row=1, "Green", IF([Percent Complete]@row<=.8, IF([Percent Complete]@row<=.4, "", "Red"), "Yellow"))

There is no need to test for values between numbers say 80-100 as I always say IF logic is like cutting a block of cheese

as you start cutting off the parts of the cheese you do not have to retest to see if it a number in the block you cut off.

For example, the first test I did is to check for 100 percent.. now everything is 99 or less. Making an assumption you have nothing over 100%

For the second test I do checks for less than 80%.. if it fails I know I have between 81 and 99. NO NEED to have both the lower and upper bounds. AND( Value > 80, Value <100) as we know it is already less than 100

The third test I did was to check for Less than 40 and if that fails I know I have a number between 41 and 80

Hope that helps

Suumit Yadav

Thanks David. It really worked. Appreciate your help with it.

Thank Brent. I was trying 2-3 different combination of percentages and got mixed up between them but you got it. Loved your analysis as well. Thanks for sharing "cheese" theory. Makes complete sense. Thanks for your help