If(AND) statement with a range

Fialko66

Stuck again getting a formula to work when using an IF(AND) statement with a range - screenshot below). On the sheet project timeline column is worked out automatically e.g. 58.43, the next column is completed by the user with 10 options. I then use the formula below to workout an overall RAG for the project depending on how far along the timeline it is and the percentage complete. Getting the below 40 for GREEN works, as does the above 80 for RED but i can't get the AMBER range to work. I have looked online at answers and it suggests using AMBER as the final argument as i have below but this returns AMBER for everything irrespective of whether the percent complete column is completed etc so i really need that last argument to be "".

Can anyone advise if there is a way around this to just display the rag when appropriate and nothing if the options are not met please

Fialko66

Managed to get it working

=IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "1-10"), "RED", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "11-20"), "RED", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "21-30"), "AMBER", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "31-40"), "AMBER", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "41-50"), "AMBER", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "51-60"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "61-70"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "71-80"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "81-90"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "91-100"), "GREEN", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "1-10"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "11-20"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "21-30"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "31-40"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "41-50"), "AMBER", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "51-60"), "AMBER", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "61-70"), "AMBER", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "71-80"), "GREEN", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "81-90"), "GREEN", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "91-100"), "GREEN", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "1-10"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "11-20"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "21-30"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "31-40"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "41-50"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "51-60"), "AMBER", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "61-70"), "AMBER", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "71-80"), "AMBER", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "81-90"), "GREEN", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "91-100"), "GREEN", ""))))))))))))))))))))))))))))))

Thanks

Fialko66

Managed to get it working

=IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "1-10"), "RED", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "11-20"), "RED", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "21-30"), "AMBER", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "31-40"), "AMBER", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "41-50"), "AMBER", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "51-60"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "61-70"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "71-80"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "81-90"), "GREEN", IF(AND([Project Timeline %]@row < 40, [Project Completion %]@row = "91-100"), "GREEN", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "1-10"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "11-20"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "21-30"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "31-40"), "RED", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "41-50"), "AMBER", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "51-60"), "AMBER", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "61-70"), "AMBER", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "71-80"), "GREEN", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "81-90"), "GREEN", IF(AND([Project Timeline %]@row < 80, [Project Completion %]@row = "91-100"), "GREEN", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "1-10"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "11-20"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "21-30"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "31-40"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "41-50"), "RED", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "51-60"), "AMBER", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "61-70"), "AMBER", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "71-80"), "AMBER", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "81-90"), "GREEN", IF(AND([Project Timeline %]@row < 100, [Project Completion %]@row = "91-100"), "GREEN", ""))))))))))))))))))))))))))))))

Thanks

Fialko66