function small for a conditional array
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
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