Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a data list from a pivot table | Excel Discussion (Misc queries) | |||
how do i create pivot table from data fields containing forumulas | Excel Worksheet Functions | |||
How do i arrange the data to create a pivot table? | Excel Discussion (Misc queries) | |||
How do you create formulas in pivot table eg simple division? | Excel Worksheet Functions | |||
I need to create pivot table for the month but data in days | Excel Discussion (Misc queries) |