How to count the highest possible occurrence in multiple tables

[JulianS96]

Hi Forum,

I've got a query about whether it's possible to only count the highest possible occurrence (Labelled "Highest") in each of the four coloured tables (In order of Catastrophic, Critical, Marginal and Negligible) and report it in the Risk Table.

This is easier shown in the attached Excel sheet.

Thanks in advance,

Julian

P.S: I'm not sure if this is possible or not, and I may have to end up manually counting the data and filtering manually etc.

[6StringJazzer]

I'm not clear on what "report it in the Risk Table" means. What is the result you expect for this particular data set? You say "The Table in Purple is the desired result" but that's just a bunch of blank cells.

[JulianS96]

I'm not clear on what "report it in the Risk Table" means. What is the result you expect for this particular data set? You say "The Table in Purple is the desired result" but that's just a bunch of blank cells.

Hi Jazzer,

Sorry I meant that the purple table is the table to the left of the purple shaded region.
When I'm on a computer again I can highlight the whole thingpurple if that makes it easier.
Sorry what I want to do is quite hard to explain in words...

What may be easier is that I make a named region and call it "Risk Table"

[6StringJazzer]

Here is the only thing I can make sense of. Please tell me if this explanation is correct.

On the right is a risk table. The top header gives the impact rating for each column. The left header gives the probability for each row. For each combination of probability and impact, there is a rating of A-B-C-D to indicate the overall severity of the risk, and a number (see below).

On the left is a table of combinations of probability and impact. Some of those combinations are marked as being "Highest" risk severity.

In the table on the right, for each combination of probability and impact, you want to count the number of times that combination is marked as "Highest" in the table on the left. That is the number that goes into the table.

The numbers highlighted in yellow in column D seem to be completely irrelevant to your question.

If this is a correct description of your question please see Sheet2 for the solution.

[JulianS96]

Hi Jazzer,
Appreciate the quick response to my query!

In regards to what you've sent, I simply put "Highest" as an indication to show which severity is the highest out of the four "coloured arrays".
I was hoping to use something like the MAX() function to find the highest number in those coloured boxes and then print it.

So the way that you have got it to work with counting the amount if it says highest, but with the weighted numbers instead in the table highlighted in yellow.

Thank in advance,

Julian

P.S: I have attached a sheet with clearer instructions to this reply.

[davsth]

I think you need to give other outcomes as it is not clear what you require, does any catastrophic beat multiple critical

IF you had a tie in a column, which value should be highlighted or should all be highlighted

perhaps j3 =IF(H3>0,0,COUNTIFS($B$2:$B$18, $F3,$C$2:$C$18,I$2)) but why is j17 1 and not 0, likewise l17?

It is easy to use finding the maximum value of the column and then returning the categories associated

[JulianS96]

Hi davsth,

Yes that is my intention. Catastrophic beats Critical, If no Catastrophic found then Critical beats Marginal, if Critical not found then Marginal beats Negligible and if no Marginal found then Negligible is used.

H17 and J17 have one occurrence where it is the maximum in the associated coloured array, specifically the Light green (B7:C10) and Light blue (B11:C14) arrays respectively.
If there is a tie in the column then all values that are a tie with the highest possible value are taken into consideration when counting.

[davsth]

To start with define the coloured groups in the first columns

some combinations such as Incredible critical appear twice in 2 different colours, what is the logic of this?

as a result of this the colours in the first table do not match directly to the risk table, so this makes interpretation difficult, and unclear what you want to return

What you require is still unclear, do the red orange yellow green colours have any bearing on what gets returned. How are combinations in 2 colures accounted for?

[JulianS96]

Gotcha.
The four coloured groups in the first columns are supposed to be separate "tables" so as follows:
Table 1 (Light orange): B2:C6
Table 2 (light green): B7:C10
Table 3 (Light blue): B11:C14
Table 4 (Orange): B15:C18

Out of each of these tables I would like the highest severity (Catastrophic=4, Critical=3, Marginal=2, Negligible=1) by the number rating (D2:D18) for each of those tables separately and then count all the times that they appear and report that count in Risk Matrix (desired result) (F11:N18)

Essentially I want to find the worst case scenario in each of the four coloured tables.
If this worst case scenario appears in one table and another table, the count should be 2 for example.
If the worst case scenario appears multiple times in one table, then the number it appears should be re

By the way, the red orange and green colours do not mean much, just that A = red, B = orange, C = yellow and D = green. They just visually show the risk but is not needed in any way for the formula section.

Hopefully this helps. if not feel free to ask more questions and i'll explain as best as I can.

[JulianS96]

I think it might be easier to explain it fully in detail what's going on here.

So the Tables:
Table 1 (Light orange): B2:C6
Table 2 (light green): B7:C10
Table 3 (Light blue): B11:C14
Table 4 (Orange): B15:C18

Are the sections with the Probability (Column B (B2:B18)) and Severity (Column C (C2:C18)).
These sections are ranked on whether or not the hazard being assessed (not included in this mockup) on it's probability if it happens, and the severity if it happens.
Column D (D2:D18) is ranking the Severity column (C2:C18) in a scale of numbers 1-4, with 4 being the worst case (Catastrophic), 3 being second worst case (Critical), 2 being 3rd worst case (Marginal) and 1 being 4th worst case (Negligible).

With Column D (D2:D18) I would like to achieve the result of counting only the items in the 4 Tables individually of their highest ranking severity (4 being the highest, then 3 and so on).

What I mean by this is that each table B2:C6, B7:C10, B11:C14, B15:C18 should be treated separately and only the highest ranking severity (Column D (Table 1: D2:D6, Table 2: D7:D10, Table 3: D11:D14, Table 4: D15:D18)) should be counted. If there are duplicates then this should be taken into the count as well.

How this works is explained on Sheet 2 with the arrows feeding into the Risk Matrix (desired result) ( F11:N17)

I would like this count to appear in Risk Matrix (desired result) (Table F11:N17) in cells:
H12:H17, J12:J17, L12:L17 and N12:N17.

To do this I would like the corresponding cell e.g. Cell H13 (Probable, Catastrophic) to match the Probability and Severity in the respective Table of B2:C6, B7:C10, B11:C14, B15:C18. For example, the greatest Severity ranking is 4 in Table B2:C6, and therefore this adds one to the count. This ranking also appears in Table B7:C10, and therefore this adds another to the count. There are no more "Probable, Catastrophic" and therefore the count stops at 2.

Can this be done?

[JulianS96]

I'll assume that this is not possible and approach the manual way of completing my request.

[JeteMc]

This has been placed in the Formulas & Functions forum, however formulas cannot distinguish between colors, so there would need to be some other way to distinguish between tables.
I have manually distinguished the tables, although there may be some other way to do so, if these are real tables which have column headers etc.
1. Change the formula in the number ratings column to read: =IFERROR(IFS($D2=$C$23,4,$D2=$C$24,3,$D2=$C$25,2,$D2=$C$26,1),"")
2. Populate the 'highest' column using: =IF(MAXIFS(E$2:E$18,B$2:B$18,B2,C$2:C$18,C2)=E2,"Highest","")
Jeff's formula will then work without having to manually identify 'highest'.
Let us know if you have any questions.

[JulianS96]

Hi JeteMC,
Thank you for your response.

This works well thank you. I've just checked through all the formulas and I'm glad to see that this works without having to specify different tables!
Thank you for this, I will add the helper columns to my main worksheet and report how it works
Really appreciate the response, I was losing hope over this, thank you.
EDIT: Works well in Master Sheet, thank you!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.