Advanced conditional fomatting

Good morning!

Okay - I have attached a screenshot of a report template that I have set up. There are two component tables: the bottom table contains details of projects by name and also by an objective code (e.g. RP01 S1, RP01 S2 etc). These relate to short, medium and long term objectives (S1, M1, L1 etc) and the prefix RP01 is the Workstream within which that project and objective are based.

The Status column records a number 1, 2, or 3 which sets a coloured flag - GREEN is on track, AMBER is off track but recoverable and RED is off track and in trouble. What I want to do is for the numbers for each OBJECTIVE to be added together to calculate a score for each Workstream. This is because a summary of the status of each Workstream is to be displayed in the top table. I want conditional rules to look for each OBJECTIVE in the objective column AND the score given for its status and combine them in some sort of pivot table. The total for each Workstream can then be set the same conditional rules and coloured flag to suggest an overall risk status (GREEN on track, AMBER off track but recoverable etc) for the top table. It will be up to managers, however to combine this with their own knowledge and make a decision as to the actual risk.

To clarify, the RP prefix in the Objective column relates to the Workstream (WS1, WS2 etc) in the top table.

So - how to make a pivot table that looks up the data I want and can display each objective and the total scores based on the STATUS in the bottom table of the main worksheet?

Any advice gratefully received!

ExcelForumQuestion2.png

Try this in B7, then fill right and down to complete your top table.

=SUMIFS($I$12:$I$21,$E$12:$E$21,SUBSTITUTE($A7,"WS","RP0")&"*",$E$12:$E$21,"*"&B$5)

Then you can apply conditional formatting as needed in the same way as you have column I.

If you need addtional help, please attach a sample workbook to your thread, we can't test formulas, etc. on a screen capture.

Thank you, Jason.b75! That worked a treat.

I am trying to apply the formula to worksheets for other teams, but have hit a problem - this lies primarily with the text in Column E. In the first example, Workstream areas were named WS01, WS02 etc and the Objectives were named with this as a prefix: RP01 S1, RP01 S2, RP02 M1 etc.

With other teams, they subdivide their Workstreams - WS1.1, WS1.2, WS1.3 and so Objectives in Column E will also use this different system.

I attach a worksheet in which I have tried to modify your formula to pick this up. I have applied it to the top half of the uppermost smaller table. I think the issue are the parameters in the SUBSTITUTE function, which I have changed to MH, but it isn't applying to individual sub-workstreams. When I try "MH1" it just collapses.

What am I not seeing?

You had some extra $ symbols, so the formula wasn't lining up correctly when you filled the table.

The formula should be

=SUMIFS($I$23:$I$35, $E$23:$E$35, SUBSTITUTE($A7, "WS", "MH")&"*",$E$23:$E$35,"*"&B$5)

not

=SUMIFS($I$23:$I$35, $E$23:$E$35, SUBSTITUTE($A$7, "WS", "MH")&"*",$E$23:$E$35,"*"&$B$5)

Once corrected, it counts 12 out of 13 records in the sample file provided. The objective in E35 doesn't match up to anything in the table.

Thank you - I need to get more experience with when to use (and indeed not to use) the $ symbol. That helps a great desl - I will have a go at applying it to the other tables, which I am sure will work now.

Many thanks!

Okay! The plot thickens with ongoing work!

I have attached two spreadsheets - EXAMPLE A and EXAMPLE B.

So - in EXAMPLE A, you will see I have added two little tables at the top-right which show the number of projects per workstream, so across each row of the upper main table, and to keep track and allow the input of multiple projects per objective - the individual cells in the main upper table.

What I am now trying to do is allow the individual objective cells, which take their values from column I, to indicate whether or not that objective is 'on track'(green), 'recoverable'(amber) etc. This is okay, only I would rather have a single integer, not a floating value, which I am sometimes getting when the total taken from column I is divided by a value in the 'multiple projects register' table.

I also want the values in the column named WS Risk Score to take a total of each row, therefore the Workstream, and give a similar on track status - only I can't work out the formula because I already have a SUM() function working there. How can I work something similar to the formulae in B6 to H6?

Okay....now for EXAMPLE B...basically trying to achieve the same thing, only I haven't set it up as far. This probably isn't as useful.

Any help VERY GRATEFULLY RECEIVED!!!!!

Just looking at example A for now,

Originally Posted by periolus

I would rather have a single integer, not a floating value, which I am sometimes getting when the total taken from column I is divided by a value in the 'multiple projects register' table.

Something like
=ROUND(SUMIFS($I$12:$I$21,$E$12:$E$21,SUBSTITUTE($A7,"WS","RP0")&"*",$E$12:$E$21,"*"&B$5)/L7,0)
Depending on the result you expect, look at ROUND (nearest integer, 0.5 or more rounds up, less rounds down), ROUNDUP and ROUNDDOWN.

Originally Posted by periolus

I also want the values in the column named WS Risk Score to take a total of each row, therefore the Workstream, and give a similar on track status - only I can't work out the formula because I already have a SUM() function working there. How can I work something similar to the formulae in B6 to H6?

Should be able to do similar here, without knowing the result that you expect from the data in the sample sheet, I don't know what else to suggest.

Thanks Jason.

I have one more question, as I think I have worked out the solution to the last problem, but can't quite work out the formula.

The column called WS Risk Score needs to be able to count the number of cells for each workstream in that top table (e.g. B7:H7, B8:H8) and divide it by the number of active objectives, i.e the number of cells in that selection (b7: h7, b8:h8) that have a numeric value in them. In EXAMPLE A, that would be 3 numbers (1,2 or 3)- so the value I would want in WS Risk Score would be the SUM (4) divided by the number of cells with active numbers (3) = 1.333

I would then set conditional formatting for the colours. The icing on the cake would be to be able to then replace the numbers in WS Score using SUBSTITUTE to show the wording you can already see in the top table "On track", "Recoverable" etc.

I tried using the SUMIFS and various versions of the COUNT function, but kept getting the wrong value (which at least showed my syntax was correct). Is there a solution?

Scrub the first part - I just deleted any formulae from empty cells and the formula =SUM(b7:h7)/COUNT(b7:h7) works fine.

I wouldn't mind checking how to substitute the text into this cell in addition though.

Thanks and sorry for the brain fart - it's been a hell of a week already!

It would be better to use LOOKUP or CHOOSE, SUBSTITUTE is not suitable for this task.

=IF(COUNT(B7:H7)=0,"",LOOKUP(SUM(B7:H7)/COUNT(B7:H7),{1,3},{"On Track","Recoverable"}))

The numbers in bold should be the lower limit for the warning flag in the equivalent position, so 1 to 2.99999 will show "On Track", 3 or more will show "Recoverable".
You can use as many numbers and warning flags as desired, but the numeric part must be in ascending order.

LOOKUP will work with any numbers, CHOOSE will only work with consecutive integers, starting from 1

=IF(SUM(B7:H7)>0,"",CHOOSE(SUM(B7:H7)/COUNT(B7:H7),"On Track","Recoverable))

Does that give you enough to work with?

Good morning!

Yes, that's great - thank you. I am working out how I might apply that same formula to the vertical columns in that table too, so that it can apply to the on track status for objectives (S1, S2 etc.). I can't get it to work at the moment, as it doesn't like empty cells.

Can that formula be easily modified to handle the vertical calculation too? with more coffee I might get it!

This is the last piece of the puzzle - if I can sort this, then I can apply it to all the templates and the job is done.

B/w
Barnaby

Can you update the sample to reflect the changes that you've made so far, both methods should work with rows or columns and should not be affected by empty cells.

Attached is the updated sheet - named "Example A_v2"

I have created entries for RP01 M3 and RP02 M3, simulating multiple projects for RP01 M3 and referencing cell R7 for single project to objective relationship for RP02 M3. I have highlighted everything related to this in pink.

In the top table, the formulae for WS Risk score works fine for the horizontal calculation, but where I have tried to mimic it in the column for M3 being on track, it doesn't pick up.

I am vexed!

I'm lost!

Where is the formula that is giving the incorrect result, and what should the correct result be?

The issue is with cells F5:F8 - the figures update from the main data table underneath, but I can't get the status indicator to change in F5 with the new formula I have put in (adapted from the horizontal indicators)

That would be because you have entered B7:B8 in the formula instead of F7:F8

Oh yeah! Thanks for that - sorry for the schoolboy error! I have so many things to tie up before I change role in a couple of weeks, that my focus is somewhat divided!

So, that should work if I replace all the on track indicators for the vertical results

Those schoolboy errors are sometimes the hardest to see.

If you select a single cell with a formula that is not giving the expected result, then go to the formulas tab, then click 'Show precedents', you will see arrows pointing to the cells that the formula is looking at. Sometimes this can help in tracing the error.