Calculating the worst entry in a group of entries

[Joe Pineapples]

Hi - I'm part of a team that undertake a control assessment every month.

We have a large number of controls which in turn can have any number of lower level sub-controls. We rate each of the sub-controls either Complete, Green, Amber or Red.

I have a simple report that runs across the sub-controls but would like to run a higher level report that shows the status of the controls. To do this I am trying to create a formula that populates a control rating based on the worst value attached to the sub-controls. See example table below.

I would be very grateful for any suggestions

Big thanks

Joe

Control	Sub-control	Sub-control rating (these are entered values)	Control rating (I would like to calculate these)
1	1.1	Red	Red
1	1.2	Amber	Red
1	1.3	Green	Red
2	2.1	Green	Amber
2	2.2	Green	Amber
2	2.3	Amber	Amber
2	2.4	Green	Amber
2	2.5	Green	Amber
2	2.6	Green	Amber
3	3.1	Complete	Green
3	3.2	Green	Green
3	3.3	Green	Green
3	3.4	Green	Green
4	4.1	Complete	Complete
4	4.2	Complete	Complete

 

[Hany ali]

wher's the file ?!!!!
and Explain exactly what you want on the file

 

[Joe Pineapples]

Sorry - file now attached (I was unable to upload from my work pc due to security settings)

I have included the request in the top of the sheet, this time making ref to the cols. I'm keen to create a formula to create the values that I have manually entered in Col D

Big thanks for any help

Joe

 

[Peter Bartholomew]

Is a helper column possible? A major difficulty arises from the fact that the ratings are text and not numbers. A helper column could provide numeric equivalents and still allow the use of MAXIFS.

The other approach is a flat out Office 365 dynamic array approach. Rather than assessing the controls in-situ the new function UNIQUE is used to provide a list of distinct controls. I then built a named range 'ratings' by using XLOOKUP to identify the sub-control rating that corresponds to the first instance of the control in the list. XLOOKUP will also return the final instance. Putting these together with the ':' (colon) range operator gives the list 'ratings'
= XLOOKUP( @Control#, Data[Control], Data[Sub-control rating], 0, 1) :
XLOOKUP( @Control#, Data[Control], Data[Sub-control rating], 0,-1)
Again the text descriptions are replaced by numbers (this time using XMATCH) but, unlike MAXIFS, the MAX function works with arrays as well as range references so the helper range is not needed.
= INDEX( List, MAX( XMATCH( Ratings, List) ) )
Without the latest version of Office 365 none of this will work and many will find it bewildering

 

[Joe Pineapples]

Peter - many thanks for this - really appreciate it. Its exactly what I was after - just need to get my head round how and why it works and apply it to my real data.

Again, many thanks

Joe

 

[p45cal]

Alternatively, array-enter one of the following formulae into cell D9:

=CHOOSE(MIN(IF($A$9:$A$23=A9,SEARCH(LEFT($C$9:$C$23,1),"RAGC"))),"Red","Amber","Green","Complete")

=INDEX({"Red","Amber","Green","Complete"},MIN(IF($A$9:$A$23=A9,SEARCH(LEFT($C$9:$C$23,1),"RAGC"))))

and copy down.
Array-entering means committing the formula to the sheet using Ctrl+Shift+Enter, not just Enter.

 

[Joe Pineapples]

Wow - that is clever. If I get the logic here you are using the Choose function (not used this before) to select one of the four labels, with the array minimum calc to define the index for Choose

Think I will run with this and see how we get on.

Big thanks both Peter and p45cal - really appreciate the advice

Best

Joe

 

[Peter Bartholomew]

@p45cal
Devious