RAG (Red/Amber/Green) Status

Excel Formulas › Dashboards & Reporting

All versionsIFS

RAG status bands a metric into Red, Amber, or Green against thresholds — the universal project and dashboard health signal. Nested IF (or IFS) assigns the band; conditional formatting paints it.

Quick formula: RAG band from a value and thresholds:
=IFS(value>=green, "Green", value>=amber, "Amber", TRUE, "Red")
Test highest threshold first: at or above green is Green, above amber is Amber, otherwise Red.

Functions used (tap for the full reference guide):

IFSIF

The example

92% completion → Green.

AB
1MetricRAG
292%Green
368%Amber

The formula

The formula:

=IFS(value>=green, "Green", value>=amber, "Amber", TRUE, "Red") // banded health status

How it works

How it works:

  1. Set two thresholds: green (good) and amber (warning); below amber is red.
  2. IFS tests highest first — at/above green → Green, at/above amber → Amber, else Red.
  3. In older Excel use nested IF: IF(v>=g,"Green",IF(v>=a,"Amber","Red")).
  4. Add conditional formatting to fill the cell red/amber/green by the text.

Direction matters. For metrics where lower is better (defects, cost, days late), flip the comparisons: green when value <= green_max. Keep the thresholds in labeled cells so reviewers can see — and adjust — what counts as red without touching the formula.

Try it: interactive demo

Live demo

Value with green≥85, amber≥60.

%
Status:

Variations

Nested IF (older Excel)

No IFS:

=IF(v>=green,"Green",IF(v>=amber,"Amber","Red"))

Lower is better

Flip the test:

=IFS(v<=green,"Green", v<=amber,"Amber", TRUE,"Red")

Numeric for icons

1/2/3 for icon sets:

=IFS(v>=green,3, v>=amber,2, TRUE,1)

Pitfalls & errors

Highest first. Order IFS/IF tests from best to worst or a lower band catches everything.

Mind direction. Flip comparisons when lower is better.

Thresholds in cells. Keep green/amber cutoffs editable, not hard-coded.

Practice workbook

📊
Download the free RAG (Red/Amber/Green) Status practice workbook
A RAG sheet with the nested-IF, lower-is-better, and numeric variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I create RAG status in Excel?
Band with IFS highest-first: =IFS(value>=green, "Green", value>=amber, "Amber", TRUE, "Red"), or nested IF in older Excel.
What if lower is better?
Flip the comparisons: =IFS(v<=green,"Green", v<=amber,"Amber", TRUE,"Red").
How do I color the RAG cell?
Add conditional-formatting rules that fill red/amber/green based on the status text.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: IFS multiple bands · KPI target indicator · Icon sets

Function references: IFSIF