Solved

function small for a conditional array

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

623 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