I have a list of store locations in column A, total sales in column B, sales percentile ranking column C.

Assume 20 rows (locations) for the example

I would like to return the min, max, and average sales amounts where percentile rank is between two values.

Can anyone help?  I am not used to Excel syntax and my brain went melty.

Hi,

Would you like to give us more information about your request >>and average sales amounts where percentile rank is between two values.<<?  Could you share a sample file?

I assume a scenario, you can see if you find it useful.

OneDrive link: http://1drv.ms/1fBOwPD

If I misunderstood something, please feel free let me know.

Regards,

George Zhao
TechNet Community Support

George,

Sorry that I didn't include a better sample.  Essentially I need to figure out the formulas to put into F2:H6 in my example here.  I am going to need answers like these for a data set that includes about 1500 rows.  Those 1500 rows will have percentiles calculated for 3 different numbers.  The data set will change monthly.  I want to know the min/max/avg for a given percentile range for each of my percentile rankings.  If I had a sample of something that will do what I need I can edit it easily to make it fit.  I just don't know Excel well enough to be able to say, "Give me the MIN value in COL B for ROWS where COL C value between 0 and 20"

I tried to figure it out myself but my (non-working) equation ended up looking like my cat fell asleep on the keyboard. (sorry about the bad copy paste but I was not allowed to post a picture.)

Location	Sales	Percentile	Percentile Range	MIN	MAX	AVG
A	300	50.00%	0 - 20	?	?	?
B	500	70.00%	20 - 40	?	?	?
C	700	90.00%	40 - 60	?	?	?
D	100	30.00%	60 - 80	?	?	?
E	20	20.00%	80 - 100	?	?	?
F	750	100.00%
G	340	60.00%
H	210	40.00%
I	11	10.00%
J	555	80.00%

Hi,

Please refer to this sample file, you can create a secondary columns to look up the corresponding value of percentage range by using IF formula, and then work out MIN\MAX\AVG values.

You can download this sample file from this link: http://1drv.ms/1HFuxWn

Hope its helpful.

Regards,

George Zhao
TechNet Community Support