?
Solved

function small for a conditional array

Posted on 2011-09-14
4
Medium Priority
?
252 Views
Last Modified: 2012-05-12
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
Comment
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
  • Learn & ask questions
4 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 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

by:daniques
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
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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;…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question