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

Anyone at all?

I am getting a looping problem here when I try to set it up... It checks for "Group 1" in N62. If TRUE, it checks N34<n45, if="" this="" is="" true,="" it="" gives="" out="" "group="" 2".="" false,="" checks="" n37<n45.="" 3".="" n38<n45.="" 4".="" n43<n45.="" 5".="" "".="" the="" problem="" now="" closing="" 1"="" false="" in="" end.="" loops="" back="" into="" 2"="" true="" will="" loop="" which="" bring="" me="" down="" all="" way="" to="" 5"="" again.="" then="" i="" have="" double="" next,="" next.

In the end, I will have gone over every cell and every check some 40 times.

In other words, I need help here, because my method makes it way too advanced...

EDIT: CRAP! When I added smaller than sign, it removed all the text behind it. So I wrote ****loads which is now gone. Basicly, where it says "it checks N34", it checks if it is smaller than N45. If TRUE, it puts out "Group 2". If FALSE, it checks the next cell (N37) against N45. This ends at one point, which is rather long, but when you then try to close the FALSE of "Group 1" from the beginning, you get looped into the FALSEs of the groups after "Group 1", every time. So "Group 5" will only have "","". "Group 4" will check get TRUE and check "Group 5"s N43 to see if it smaller than N45, which is already done. You can see where this is heading, when you get to the last, which would be "Group 2", you would have to loop into "Group 3", which would have to loop into "Group 4", which would have to loop into "Group 5", which gives "" again. So the previous "Group 3" would loop into "Group 4" and "Group 5" the same way, and "Group 4" will as mentioned do this as well, but that one is rather short.
</n45,>

I think I'm misreading something in your post, to try and show this clearly, I'm going to use different ranges.

Assuming that you have Groups in A2:A5 and their respective values in B2:B5, average of B2:B5 in B6.

In A8 enter

=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)),"")

Which must be array confirmed using Shift Ctrl Enter, not just Enter. Next use the fill handle to copy the formula down to A11.

In B8 enter =IFERROR(VLOOKUP(A8,$A$2:$B$5,2,0),"") and fill down to B11.

Does that help?

I tried it now. THe problem with my Excel is that I got the US version (I think, or EU), anywho, it wants ; inplace of ,. When I edit those, I get parenthesis error.

The weird part is, when I put it into Google Spreadsheets instead, it works, but if I put test values 1 4 0 6 respectively, it pops up with Group 3 in A8, which is not correct, as Group 1 got the value of "1", which is below the avg of 1,4 0 and 6 (which is 2.75).

I will try to re-explain with using the full table that I got for this particular test.

The number from each group is how many from each group attended an event, and the average is the count (total from all groups) divided on the number of events we host per year. There are more of these, as you probably can guess, since loads of them are set as N/A, they are in the next colums, for a total of four columns.

Anyways. THis is what I want:
These 3 groups are picked out because they are below the average. So basicly, the first of these (here 3) cells checks if Group 1 is below avg, if not, then check group 2, and so on, except those N/A obviously. This is pretty much straight forward with a simple

=if(N33<N45,"Group 1",if(N34<N45,"Group 3",if(N37<N45,"Group 5",. . .

Click to expand...

The problem comes when the next cell comes along. Here I want it to check as above, but if there is any Group in the first cell, it will not be mentioned.

So basicly, you could look at it this way: If Cell 1 got "Group 2", it will not be used in Cell 2, as it has been "used up" in Cell 1 already, and doesn't need to be used again. So it moves along. Is "Group 2" below avg AND used in Cell 1? If yes, move on, if no, Cell 2 will be populated by "Group 2" if it is below the average. Cell 3 will check if Cell 1 got any group, and if it does, not repeat it. Same for Cell 2. So in the end, if all but one were below avg, they would all be listed, for a total of four. If only one was below, I would only get one cell in the "The following groups are dragging the average down and needs attention:" part.

Hope this clears some stuff up. Probably not, I suck balls at explaining stuff like this.

It all makes sense, I made a silly mistake in one of the formula. The first one should have been

=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)));"")

See if that works any better.

Okay, tested it now, it works perfect! But, I have to twist your brain a bit more actually. I can use this in my personal spreadsheet, which is fine, I can upload it, but if I want to use it as a collabed spreadsheet, I must use Google.

And for some reason, it doesn't want to give me any output. Is there any other way to do it that does not use array? =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($B$8:$B8))),"")) is what it reads, so it is correct, except it doesn't use {} for Array, but its own formula in the start instead.

Hmm, letting the guys see all this is getting hard it seems, without doing it oldschool with uploading and downloading and notifying on each update that comes out, for instance.

Okay, in the end, where you got "", which is the value_if_error from iferror, I wrote "Something Wrong", just to test.

The output I got was Something Wrong in all cells. So clearly it isn't too happy about something, since it gives out what we would consider here as "Whatever is true for iferror is not happening".

I can't see why it happens....

Ah, I found some info that might help:
Question:

I'd like to know which column is the last column where the sum of the values of the row is smaller or equal to a given value. (Count the columns until a sum is reached)In Microsoft Excel the following array formula works just fine:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">{=MATCH(7;SUBTOTAL(9;OFFSET(C1;;;1;COLUMN(C1:G1)-COLUMN(C1)+1));1)}</code>

Click to expand...

But Google Spreadsheet always returns 1 as an answer

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">=ARRAYFORMULA(MATCH(7;SUBTOTAL(9;OFFSET(C1;;;1;COLUMN(C1:G1)-COLUMN(C1)+1));1))</code>

Click to expand...

Is there some difference between Excel and Google Spreadsheet array formulas that I'm missing? If there is a difference is it documented somewhere?
Is there another way to implement this in Google Spreadsheet (preferably without custom functions)?

Click to expand...

Answer:

Is there some difference between Excel and Google Spreadsheet array formulas that I'm missing?

Click to expand...

The difference is how specific functions are supported in array formulae. In this case, you're out of luck on two counts: OFFSET can't be iterated over an array (ie it can't produce an "array of arrays" as it can in Excel), and the second argument of SUBTOTAL can't be iterated either; in Sheets, it must be an explicit range.

If there is a difference is it documented somewhere?

Click to expand...

No, not that I know of.

Is there another way to implement this in Google Spreadsheet (preferably without custom functions)?

Click to expand...

<code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background: rgb(238, 238, 238);">=ArrayFormula(MATCH(7;SUMIF(COLUMN(C1:G1);"<="&COLUMN(C1:G1);C1:G1)))</code>

Click to expand...

Specificly, I am looking at this:

In this case, you're out of luck on two counts: OFFSET can't be iterated over an array (ie it can't produce an "array of arrays" as it can in Excel), and the second argument of SUBTOTAL can't be iterated either; in Sheets, it must be an explicit range.

Click to expand...

The SMALL function you are using is referring to an array inside an array, as mentioned here, array of arrays.

Any way around that, and we are goal!

Unfortunately my brain doesn't twist as far as google docs, so I can't say why it doesn't work there.

See if this works for a non array version,

=IF(SMALL($B$2:$B$5,ROWS($B$8:$B8))<$B$6,INDEX($A$2:$A$5,MATCH(SMALL($B$2:$B$5,ROWS($B$8:$B8)),$B$2:$B$5,0)),"")

I think that the cells with N/A might cause problems though.

They might, they may not. If I leave them totally empty, as blank, will that be a problem? I could just have them black and no text just as well.