PDA

View Full Version : Count Number of Targets Reached



michaelrjb
08-28-2013, 08:47 AM
Hi All

I have a workbook that shows the RAG status of a number of projects i am working on.

The 'Detailed' Worksheet has a 50+ projects in it, each with a number of objectives for each project.

The 'Summary' worksheet lists all the project and an indicator (RAG) showing how many objectives have been met for that project. So if an objective is below 70% complete it should tally 1 to the Red status, over 80 tally to yellow and over 90% tallies to green.

I tally all these up by hand, and i'm hoping there is some quick SUM statement that will give me a figure of the number of objectives that are at green.

So for the W7 Deployment Project, it may have objectives such as 'Deploy to Site 02' on 'Detailed:B3' @ 98%, 'Deploy to Site 03' in 'Detailed:B4 @ 56% and 'Deploy to Site 04' on 'Detailed:B5' @ 91%, so the Red Status on 'Summary' should be 1, and the Green 'Status' should be 2.

Thanks for any help.

10513

SamT
08-28-2013, 09:44 AM
IF...you are willing to fill column a with the appropriate project names, (ie; A4:A10 = W7 Deployment.) YOu can format the lower Cells to deemphasized them by using a smaller forn, italics or using a less obvious font color. I dis-recommend using white. Instead use a pale pastel or grey. You can use Tools >> Options >> Color >> Custom to adjust any color.

THEN... you can use a function formula in each of the RAG cells

Unfortunately...although, I was able to put a simple CSE formula in column "B," I am a coder, and not a formulater. But this will serve to bump your post,

p45cal
08-29-2013, 02:03 AM
As SamT says, filling column A with repeating project names (this makes it more like a database) will make any formulae much easier to maintain.
There is a short macro in the attached which makes a copy of your Detailed sheet, fills up column A, deletes a few redundant rows and adds a formula to column D to give the RAG status. I've had to guess a bit with this formula because
if an objective is below 70% complete it should tally 1 to the Red status, over 80 tally to yellow and over 90% tallies to green there's a range of values unaccounted for (what rag status does a value from 70 to 80 have?). The formula is like:
=INDEX({"R";"A";"G"},MATCH(C3,{0;80;90},1))
where you should adjust the thresholds {0;80;90} bit to perhaps {0;70;90}, whatever. You can do this in the code too.

On the Summary sheet formulae:

Cell Formula
B5 =COUNTIF('Detailed(2)'!$A$3:$A$12,Summary!A5)
C5 =COUNTIFS('Detailed(2)'!$A$3:$A$12,$A5,'Detailed(2)'!$D$3:$D$12,"R")
D5 =COUNTIFS('Detailed(2)'!$A$3:$A$12,$A5,'Detailed(2)'!$D$3:$D$12,"A")
E5 =COUNTIFS('Detailed(2)'!$A$3:$A$12,$A5,'Detailed(2)'!$D$3:$D$12,"G")

copied down.

ps. On the newly created sheet in the attached, I also put in a simple Pivot Table (since the Database format lends itself to this) which took 30 seconds to create and gives you the same information in a different layout.