Looking max+if with in a limited range

[SwissExcel]

Hi forum,
I have created a max+if and Min+if array that looks for the max and min paying prices per each product/customer transaction (see attache excel).
However, my data base is not "clean". It contains lot of small values that distort the result.
The way to go over this problem is by looking only on the top customers (top 75% in terms of qnt).

I have created pivot and OFFSET table to do that, but I am sure there is a simple easy way to do what I want to do
What I am basically trying to do is to create max+if within a limited range.


Would appreciate your support.

regards

SwissExcel

[daffodil11]

Perhaps:

L:

Please Login or Register  to view this content.

M:

Please Login or Register  to view this content.

This reads: If (each Value in C3:C38 > first quartile of C3:C38) AND (A3:A38 = K), then E3:E38.

[FlameRetired]

I made a copy of your output table in the attached file.

In M3 this array-entered formula:

Please Login or Register  to view this content.

and filled down.
In N3 this array-entered formula:

Please Login or Register  to view this content.

and filled down.

Array entered formulas are not committed in the usual way. To do so in edit mode while pressing and holding Ctrl + Shift hit Enter.

BTW I these formulas yield figures different from your upload. I did a cursory examination of the data and formula results. What little I examined is correct.

[SwissExcel]

Hi Daffodil and FlameRetired
Many thanks for your answers (which are basically the same - one is with percentile and the other with QUARTILE.exc). ,
Both of the formula work except of one problem: when I have only one value (i.e one product+customer combination) the formula doesnt work. I have used PERCENTILE.INC to try to go over the problem, but still it doesn't work.
Can you advise (attached is an updated excel v2. I have added a product and marked in yellow to illustrate the problem)

Cheer
SwissExcel

[daffodil11]

The only thing I can think of would be to slightly bend the parameter of the percentile, and change the expressions to >=.

This would technically include the 1 percentile >24.99999999%, but it will allow you to prevent the error.

[SwissExcel]

Thanks for quick replay. How would you put it in the formula?

Cheers
Ofer

[daffodil11]

rainbow unicorn method:

Please Login or Register  to view this content.

FlameRetired method:

Please Login or Register  to view this content.

[SwissExcel]

Many thanks
Much appreciated