Solved

function small for a conditional array

Posted on 2011-09-14
234 Views
Hi!
I have a test where the results depend on certain parameters such as sizes (S, M, L), colours (R, Y and G), and finally shapes (Round or Flat).
The results have been tabulated and I now wish to group some results based on one or 2 of the parameters so that for example, I would get one column for all the values I obtained for the small green items ( fat or round), another for the small Red items, and one column for the small yellow items.
I need the values to be then sorted from smallest to largest in each column. The latter I can do using small, and if the columns were grouped easily, I could simply use the "SMALL" function, with the range spanning over the required columns, and I would get my column. My problem is when you want to change the conditions, you then need to reshuffle the columns to group them accordingly. The only other option I could think of was using an "IF" statement, but then the “SMALL” function needs to be entered as an array formula which slows down the calculations dramatically. Is there an easy way to get about this? I am quite desperate as there are quite a few trials and I am at loss as how to proceed with the least possible manual “shuffling” to ensure there is no mistakes!
Thanks for helping me out!
Danièle
I have attached a sample sheet to help. small-example.xlsm
0
Question by:daniques
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 500 total points
ID: 36541781
Hello daniques,

I'm not sure how much this helps you, but SMALL does actually accept a "union" as the first argument which allows you to specify non-contiguous ranges, you just need to enclose the separate ranges in parentheses separated by commas. For example, asssuming you wanted ROUND, YELLOW items then you can use this non-array formula in U5 copied down

=IFERROR(SMALL((C\$4:C\$16,F\$4:F\$16,I\$4:I\$16),ROWS(U\$5:U5)),"")

This part is the union

(C\$4:C\$16,F\$4:F\$16,I\$4:I\$16)

Note that using IFERROR (available in Excel 2007 and later) rather than ISERROR allows you to error-check without repeating the main function.....and I also used ROWS in place of ROW which is a little more robust in case you add rows to the worksheet, see attached example

Of course, using this method the formulas don't automatically pick up the criteria in the headers so if you change the criteria you still have to change the ranges in the formulas.

If you wanted to retain the array formulas you could make those more efficient by using IFERROR as i have done.

regards, barry
27309319.xlsm
0

Author Comment

ID: 36541871
Hi Barry,
I had tried the union and yes, like you mentioned if does not pick up the criteria from the header which is the problem. To pick up from the header ensures that there are no manual mistakes.
Thanks for pointing out the "IFERROR" and the rows tip. That should save a lot of hassle. Thank you so much for that, and I will straight away apply them.
I wish "SMALL" could have the same as with the averages, "SMALLIF" and "SMALLIFS".... Maybe next excel? Well, I am for the time being sticking with the IF statements and the array formulas as a safeguard to human error, but maybe will need some macro? I am afraid I would be incapable of writing one, but I suspect that would be the best time saving possibility?
Barry, many thanks again for solving some of the “slowness” issues.
Cheers,
Danièle
0

LVL 50

Expert Comment

ID: 37412244
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month4 days, 23 hours left to enroll