Solved

function small for a conditional array

Posted on 2011-09-14
4
205 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
4 Comments
 
LVL 50

Accepted Solution

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

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

Expert Comment

by:Ingeborg Hawighorst
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now