Extract Largest and Smallest Items with Conditions

[andrewc]

Hi,

I have sample data in A1:G11 and would like to create formulae to extract the 3 products with the largest and smallest growth rates as shown in E2:G10.

The conditions associated with this are:

Any product with the name "JJJ" will not be considered nor will any product with a zero Sales AND Growth value (like DDD).

Can someone please suggest formulae to achieve this.

Thanks!

[Kaper]

For both Ex2010 and 2019:

G3 array formula:

Please Login or Register  to view this content.

and G8:

Please Login or Register  to view this content.

both copied down.

And E3 and copied down, and right, and to the next section - regular formula:

Please Login or Register  to view this content.

[andrewc]

That's great, thanks Kaper.

Can I please ask one more, related question:

How would I adapt these formulae if there were duplicate values in column C? For example, GGG had the same value as CCC?

Thanks!

[Kaper]

One of possible solutions (for both Excel 2010 and 2019 could be array formula in E3, copied down, right and to the second section:

Please Login or Register  to view this content.

Note 1 in first argument.

And that case shows clearly why I always ask:

Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, etc. - please show them all or at least indicate in text). The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.

[andrewc]

Thank you!