Look within a large array of values for a small array that meets a certain criteria

[sean2222]

Hi everybody, I have attached an example spreadsheet with a set of data that I would typically get on sheet1 and then what I am hoping to to accomplish on sheet2. Basically, I have one large array of values, called "RAW DATA", with multiple sub-groups inside and I am trying to look for those sub-groups and get their means and standard deviations for each row #. Each sub-group has its own "Name" (1A, 1B, 1C, 2A, 2B, 2C) and set of sequence values (1 through 5). My goal is to try and automate this process of going in and selecting sets of data from each sub-group to be analyzed for mean and standard deviation. The tricky part is that the sub-groups come in all different array sizes for different sets of raw data, so the number of serials could be 5, 20, 200, etc... My initial idea was to use the sequence numbers "1" as a signal for the start of the next sub-group and that all data within that row up until the cell just before the next "1" in the sequence would be used for the mean and standard deviation calculation. I would appreciate any help that I can get!

Note: The raw data spacers in sheet2 are optional (just looks nicer). Sheet2 is just there to show what I would like to have as my end result.

[loginjmor]

Hi -

Copy this array formula (meaning you have to press Ctrl-Shift-Enter from the formula editor window to engage the array functionality - If you did it correctly, Excel puts curly braces {} around the formula) into Cell C10 and copy across for the average:

=AVERAGE(INDIRECT(ADDRESS(ROW(),SMALL(IF($U$6:$BC$6=C$8,COLUMN($U$6:$BC$6)),1))&":"&ADDRESS(ROW(),LARGE(IF($U$6:$BC$6=C$8,COLUMN($U$6:$BC$6)),1))))

Copy this one into the Standard Deviation section:

=STDEV.P(INDIRECT(ADDRESS(ROW(),SMALL(IF($U$6:$BC$6=G$8,COLUMN($U$6:$BC$6)),1))&":"&ADDRESS(ROW(),LARGE(IF($U$6:$BC$6=G$8,COLUMN($U$6:$BC$6)),1))))

I didn't do the whole file, but I did Group 1, Section A,B and C. I had to insert a row to create a column heading I could use to search the raw data easily. This will search your Name Row in the Raw data for as many columns as contains each name (e.b., 1A, 1B, 1C, etc.) and creates an address for the range to perform the calculations.

Attached is that file.

Hope this helps.

[sean2222]

Thank you! This worked great. Could you actually explain what each of the functions within the Average and standard deviation functions did?

[loginjmor]

Hi -

Sure! The trick is to have Excel create the address range for each group of numbers you want to average (or Std Dev). So we use the ADDRESS function that will create a text string that looks like an Excel address such as U10:Y10 (which is our first range of data in the example).

The ADDRESS function wants the row number first, so I just use the current row that the data and the formula are in using the ROW() function. Using ROW() without any cell information within the parens () just returns the current row. So if my formula is in Row 10, ROW() returns 10. That's the first part of the address.

The second part I need is the columns. This gets a little trickier because I need a starting column and an ending column. I also need an IF statement to check where my data range starts and where it stops. You had set up the ranges in Row 6 to have the Name of each data group listed there, and that's what we have Excel search to find the starting and ending columns. For the very first cell we use the IF statement to check the entire range in Row 6 to see if any of those Names match "1A". If there is a match, then I have Excel execute the COLUMN function to tell me the column number of the match. In fact, we have 5 matches: Columns U, V, W, X, and Y. This means we have an array of columns to pick from (which is why we had to use an Array formula to get the IF statement to look at a range of columns rather than one column at a time). Finally, we have to pick the smallest column number that is a match (Column U) and the largest column number that is a match (Y). Guess what? Excel has functions SMALL() and LARGE() that do exactly that. They pick the nth smallest or largest out of a list (or array). Since I want the smallest and largest, I put a 1 in the last position of the SMALL() and LARGE() functions. If I put a 2 in there, I would get the second smallest or largest, and so on. So, long story short, nesting the IF statement that generates a list of columns that match the search criteria, within the SMALL or LARGE functions, I get the column numbers I am looking for to create the text string "U10:Y10"

If you look closely at the formula, you will see &":"& in the middle that separates the SMALL and LARGE portions of the address building formula. So the first ADDRESS function will return U10 (the smallest end of the range) and the second ADDRESS function will return the Y10 (the largest end of the range). We have to add the colon between them to create a string that looks like an address range. That's what that middle piece &":"& does. The & symbols tell Excel to concatenate the text pieces together. So then we get the final product of the middle section of the formula "U10:Y10"

Unfortunately, we did all that work for a String: "U10:Y10" which is great, but utterly useless to almost all excel functions , Except the INDIRECT function which takes an Excel address String and changes it into cell references that most Excel functions recognize (in this case the AVERAGE and STDEV.P functions). So that's the last piece is to use INDIRECT to convert the text strings that Excel builds using all those other functions and searches into an address range that is usable by the other Excel functions. And that's basically it. The first formula is the hard part to build. But if you're careful, once you build the first one, it's simply a matter of copying it everywhere else.

Hope that helps. Let me know if you have other questions!

[sean2222]

Actually I have one last question. In some instances I will not have data for group 2, so I will get errors in the mean and standard deviation columns for that group, which is fine, but I wish for it to return either blank ("") or "na" instead of the error. To account for both cases where I either have or don't have group 2 data I did an =IFERROR(AVERAGE(INDIRECT(ADDRESS(ROW(),SMALL(IF($U$6:$BC$6=K$8,COLUMN($U$6:$BC$6)),1))&":"&ADDRESS(ROW(),LARGE(IF($U$6:$BC$6=K$8,COLUMN($U$6:$BC$6)),1)))),""), which works (sort of) for the mean calculation but doesn't for the standard deviation calculation. Both work fine when there's no group 2 data, but when I do have the data the mean calculates a different value and the standard deviation gives me a blank. The attached worksheet shows in sheet2 the calculation for group 2 with the IFERROR function and directly below the values it should read when there is data.

I hoped the IFERROR function would have no impact when there is data but clearly I was wrong on that. Any insight on how to fix this would be great!

[loginjmor]

Hi -

Using the *ERROR testing functions (ISERROR, IFERROR, etc.) in an array formula can be problematic because some parts of the array may be OK and other parts not, but the *ERROR function can't differentiate so it just returns an array with errors. It's especially difficult with these two formulas because you are building addresses. It's not like you can easily check an address range to see if it's blank.

So, we're going to use a trick. We are going to keep the original formulas we had but we are going to use Conditional Formatting to "hide" any error messages (e.g., "DIV/0!" , etc.). I have modified your latest workbook to restore all of the formulas to just AVERAGE and STDEV without the IFERROR addition. Then I deleted the data in 2C. So now the Mean and Std. Dev. for columns M and Q show #DIV/0!.

Next, I highlight the ranges C10:Q29 (all of the calculation area). Then I go to Conditional Formatting on the Home Ribbon and expand the pulldown menu. Select "New Rule" and then select "Use a formula to determine which cells to format". In the Edit the Rule Description Box, there is a window titled "Format values where this formula is true:" Select in the formula editing box. Type this formula:

=ISERROR(C10) Make sure the cell reference C10 is relative (Not $C$10)

Next, hit the Format button. Select the Font tab at the top. Hit the Color button and select the background color (in this case white). Hit OK and OK. Voila! All of the errors have disappeared! Actually what we did is format the cells so if a cell contains an error message, we turn the text the same color as the background so you just can't see it. The formulas and errors are still there, but just invisible.

I have attached your example sheet with this conditional formatting applied.

Hope this helps.

[sean2222]

Hi, I appreciate you putting in the effort to figure out this trick. I actually should have been more specific. The reason why I am trying to embed the IFERROR function into the cell is because I have another function in columns F through K that calls the cells for (1A,1B,1C,2A,2B,2C). I included them in the sheet and they are titled A,B,C mean and sigma delta. I also included a group 3 to better show what I am trying to do. Basically the formula in columns F through K reads max and mins for all three groups and performs a calculation given that a set of conditions are met. The problem is when there is no group 3 data, I get that error as mentioned above, which results in the formula in columns F --> K to give me errors as well. So my hope was that by making group 3 means and standard deviations read blank ("") by using the IFERROR function, the formula would read that there is nothing there and just look at the max and mins in groups 1 and 2. If the array function can't be modified perhaps the formula in columns F-->K can be?

[loginjmor]

Hi -

OK - I couldn't find a way to incorporate the IFERROR function into either formula. However, I did insert a Helper column that does this error check and inserts a blank "" if one or more of the cells is some kind of error. Then I adjusted the adress references in your Mean Delta and Sigma formulas to look at the helper column rather than the original calculated columns. To make it a little more readable, I shaded the calculated Mean columns blue and the calculated Std. Dev columns red. You can always hide those columns if you would like.

Attached is the revised spreadsheet that should take care of the problem you describe.

Hope this helps!