Hi  I need help with this formula. I am getting UNPARSEABLE error with it each time
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.
Best Answer

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"))
Answers

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"))

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
10080 > Yellow
7940 > 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 80100 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
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com

Thanks David. It really worked. Appreciate your help with it.
Thank Brent. I was trying 23 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
Help Article Resources
Categories
Check out the Formula Handbook template!