Rank.EQ formula with IF conditions

[pherman]

Hello! I've been pretty stumped on this formula, and hoping you might be able to give some guidance or knowledge on this one! I'd like to write a formula that compares a dealership's comparison to the total dealer average when it comes to their response rate and total sales response in percentiles (red<85%, yellow 85% - 105%, and green over 105%).

How do I know if the formula I wrote is pulling in that specific dealer's ranking? This data set has 4,000+ rows of dealer data, so the data may not always be static in that the dealer order may vary month to month.

=RANK.EQ($D3,$D:$D,1)/COUNT('12 Mo View '!$D:$D)

Column A	Column B	Column C	Column D	Column E
Region	Dealer	BAC	Response Rate	Total Sales Per Response
10	Hometown Motors	123456​	39.54%​	$363.89​
10	Test Dealer A	311245​	9.07%​	$397.49​
10	Test Dealer B	321689​	15.49%​	$203.98​

Also, if I wanted to compare the dealer's ranking compared to dealers in region "10" only, how do I edit the ranking formula to include this specific criteria?

 

[StephenCrump]

Welcome to the Forum!

Sorry, I am not clear what you're trying to do, or why you are using RANK.EQ(). I suspect this is why you've had no responses.

Based on your description, I am guessing you want:

	A	B	C	D	E	F
1	Region	Dealer		Response Rate	Overall	Region
2	1	A		39%	124%	128%
3	1	B		20%	64%	66%
4	1	C		31%	99%	102%
5	2	D		30%	96%	87%
6	2	E		33%	105%	96%
7	1	F		32%	102%	105%
8	2	G		40%	128%	117%
9	3	H		24%	77%	84%
10	3	H		33%	105%	116%
11
12			Averages
13			1	30.50%
14			2	34.33%
15			3	28.50%
16			ALL	31.33%
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=D2/AVERAGE(D$2:D$10)
F2:F10	F2	=D2/AVERAGE(IF(A$2:A$10=A2,D$2:D$10))
D13:D15	D13	=AVERAGE(IF(A$2:A$10=C13,D$2:D$10))
D16	D16	=AVERAGE(D2:D10)

e.g. in row 2, the dealer response rate of 39% is 124% of the overall average 31.33%, or 128% of the region 1 average 30.50%.

You could use these formulae to conditionally format red/yellow/green.

 

[pherman]

Thank you so much for your reply. This is exactly what I was looking for! When I type in the regional formula using the AVERAGEIF equation, for some reason, I'm still receiving the same answers for the overall percentages. I typed the formula exactly as you wrote it above, ensuring that I didn't lock the formula in places where I shouldn't have. Any thoughts on why these formulas aren't producing different results? Thanks so much again for your assistance with this!

 

[StephenCrump]

I wasn't sure you'd have the AVERAGEIF function. It's a good idea to edit your profile to show your Office Version and Platform (e.g. I am showing 365/Windows) so that we can customise the best solutions.

Here's the same layout using AVERAGEIF:

	A	B	C	D	E	F
1	Region	Dealer		Response Rate	Overall	Region
2	1	A		39%	124%	128%
3	1	B		20%	64%	66%
4	1	C		31%	99%	102%
5	2	D		30%	96%	87%
6	2	E		33%	105%	96%
7	1	F		32%	102%	105%
8	2	G		40%	128%	117%
9	3	H		24%	77%	84%
10	3	H		33%	105%	116%
11
12			Averages
13			1	30.50%
14			2	34.33%
15			3	28.50%
16			ALL	31.33%
Sheet2

Cell Formulas
Range	Formula
E2:E10	E2	=D2/AVERAGE(D$2:D$10)
F2:F10	F2	=D2/AVERAGEIF(A$2:A$10,A2,D$2:D$10)
D13:D15	D13	=AVERAGEIF(A$2:A$10,C13,D$2:D$10)
D16	D16	=AVERAGE(D2:D10)

If you hover over the square to the top left of A1, you have the option to copy this into a workbook, i.e. no need to retype.

If you have another layout where you can't get the formula to work, perhaps you can post? Ideally you'll use the XL2BB add-in which you can download here: XL2BB - Excel Range to BBCode