Four cells with potential problems, need the problematic ones listed one by one below

[JakkeJakobsen]

Group 1	1.625
Group 2	0.75

<colgroup><col style="width: 143px"><col width="114"></colgroup><tbody>
</tbody>

Group 3

0

<colgroup><col style="width: 143px"><col width="114"></colgroup><tbody>
</tbody>

Average

0.969

<colgroup><col style="width: 143px"><col width="114"></colgroup><tbody>
</tbody>

There are four groups, but for simplicity I will use three as it will give the same output

Now, below all this, I have this:

The following groups are dragging the average down and needs attention:

Group 2

<colgroup><col style="width: 114px"></colgroup><tbody>
</tbody>

Now, below "Group 2", which is cell N62 btw, I want the next below average group listed, which in this example would be "Group 3".

However, if "Group 1" was a problem as well, it would have been in N62, and "Group 2" would have been in N63 instead.

Basicly I want N63 to do the same as N62, but exclude whatever is in N62, so it will jump to the next group instead and list that, so I get a complete cell by cell list of what group is a problem...

This is because I have colors for each group to identify them more easily, so I'd want to keep that through the whole sheet.

Cheers,
Jim

 

[JakkeJakobsen]

Well. ****, spelled wrongly.

For some reason my brain didn't wrap around the IDIOTIC mistake of having the avg in B7 instead of B6. It just made sense to have the avg just above the "cell checkers" you made. O to the M to the G!

 

[JakkeJakobsen]

Yeah, now the =ArrayFormula(IFERROR(INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$B$6,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($A$8:$A8))),"")) works, because I was stupid. Apparently, array in array works (if SMALL is an array as the guy from the question post I had here was correct. I am confused about what I am writing now. I will test it in the full version of my sheet, brb)

Anywho, sorry about that, Major Screwup at your service!

 

[jasonb75]

Blank cells would work fine, but I've seen an anomaly where a variation of N/A typed into a cell is misinterpreted by excel as #N/A, which is a formula error.

Test it with one N/A in the list, if it's going to be a problem, then that would turn every result to #N/A.

 

[JakkeJakobsen]

It works with the N/A in it as well, thank god. Well, it works in Google Docs, don't know about Excel for that version of my list. I started this project in Excel then migrated it to Google Docs, so it isn't as updated and fancy looking in Excel as it is in Google Docs.

 

[jasonb75]

You also said you wanted to copy the colour coding to the results?

If that is done on the main table with conditional formatting then you could just apply the same rules.

If you colour coded the cells manually then it would need vba, which I don't think works with google docs.