Using Pivot Table Data Values to Create Formulas
 

I have a spreadsheet used to capture and measure benefits. Each time a
benefit is measured, the result is captured in a new column (Status 1 for the
first measure, Status 2 for the second measure and so on) and a status of
"Red", "Amber" or "Green" is allocated (from a dropdown list). The
spreadsheet also contains a calculated field that pulls back the most recent
status. The key column headings that I use are listed below:

* Current Status (shows the data in Status 3 if that column is populated, if
not shows the data in Status 2 if that column is populated, if not shows the
data in Status 1 if that column is populated. If none are populated, it
shows the words "Not Started")
* Project
* Benefit
* Status 1
* Status 2
* Status 3

I use a pivot table to tell me about Current Status by Project. A Project
can have more than one benefit.

I need to understand the overall Project status. This is based on % of
benefits that are Red and % of benefits that are Green. A project is Red if
33% or more benefits are Red, it is Green if 68% or more are Green, else it
is Amber.

The if statement that I use to show me this is approximately

=if(RED STATUS CELL =67%, €œRed€�, if(GREEN STATUS CELL 33%, €œGreen€�,
€œAmber€�))

The if statement works if I have a Red status somewhere in the Current
Status column. The problem is if all my statuses are Green, or a mix of
Green and Amber I get an error.

So, finally the question. Can I either create a formula based on pivot
table data values that will still return the right status if some of those
values aren't actually active in the sheet? Or, is there a better way to
write my if statement (oh, my colours and % values are taken from constants)?

Thanks for your help!

Kat