Solved

function small for a conditional array

Posted on 2011-09-14
4
214 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

809 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