Large Function with Absolute Value and sort based on Original Value

[nrodrigues23]

I have a range of 18 variances that I am looking to organize. My goal is to take the top 10 variances (positive or negative) and once they are identified, sorted from greatest to least (sorting their original values). Trying to work through this formula and this is far as i have gotten.
=LARGE(ABS(IF(OPEX_Summary_Desc<>"",OPEX_Sum_Variance_vs_Bud)),{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
This formula has allowed me to identify the largest variances but it sorts based on the Absolute value that the formula is finding.
Any suggestions would be greatly appreciated.

 

[Eric W]

A bit tricky, but how about:

Book1
	B	C	D	E	F
1
2	a		1		-12
3	b		4		11
4			5		-10
5	d		-4		9
6	e		4		9
7	f		9		8
8	g		-2		-6
9	h		-10		-4
10	i		-6		4
11	j		8		-4
12	k		2		4
13	l		-2		2
14	m		11		-2
15	n		2		2
16	o		-4		-2
17	p		-12		1
18	q		9		0
19	r		0
Sheet1

Cell Formulas
Range	Formula
F2:F19	F2	{=IFERROR(INDEX(OPEX_Sum_Variance_vs_Bud,MOD(LARGE(IF(OPEX_Summary_Desc<>"",MMULT(--(ABS(OPEX_Sum_Variance_vs_Bud)>TRANSPOSE(ABS(OPEX_Sum_Variance_vs_Bud))),ROW(OPEX_Summary_Desc)^0)*100+ROW(OPEX_Sum_Variance_vs_Bud)-ROW(INDEX(OPEX_Sum_Variance_vs_Bud,1))+1),ROWS($F$2:$F2)),100)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[nrodrigues23]

Seems to work to find the values but I need the values to sort from greatest to least. To reference your example above, I would like to take the top 10 variances from column F and sort them from greatest to smallest (ie,. 11,9,8,4,2,-2,-4,-6,-10,-12).
Thanks so much for the help. let me know if you think this is possible.

 

[Eric W]

That's actually much easier:

Book1
	B	C	D	E	F
1
2	a		1		11
3	b		4		9
4			5		9
5	d		-4		8
6	e		4		4
7	f		9		4
8	g		-2		2
9	h		-10		2
10	i		-6		1
11	j		8		0
12	k		2		-2
13	l		-2		-2
14	m		11		-4
15	n		2		-4
16	o		-4		-6
17	p		-12		-10
18	q		9		-12
19	r		0
Sheet1

Cell Formulas
Range	Formula
F2:F19	F2	{=IFERROR(LARGE(IF(OPEX_Summary_Desc<>"",OPEX_Sum_Variance_vs_Bud),ROWS(F$2:F2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[nrodrigues23]

How would you go about limiting the range to the top 10 variances? Keep in mind, I cant take the top 5 negative variances and top 5 positive variances. I would need the largest 10 variances (absolute value). Sorted positive to negative (like your last post).
Thanks a lot

 

[Eric W]

Only put the formula in the top 10 cells. In this example, delete everything from F12 down.

 

[nrodrigues23]

Only put the formula in the top 10 cells. In this example, delete everything from F12 down.

I am looking to the largest 10 variances (absolute value).Keep in mind, I cant take the top 5 negative variances and top 5 positive variances. They would then need to be sorted positive to negative (like your last post).
Thanks a lot

 

[Eric W]

OK, let's try this:

Book1
	B	C	D	E	F
1
2	a		1		11
3	b		4		9
4			5		9
5	d		-4		8
6	e		4		4
7	f		9		4
8	g		-2		-4
9	h		-10		-4
10	i		-6		-6
11	j		8		-10
12	k		2		-12
13	l		-2
14	m		11
15	n		2
16	o		-4
17	p		-12
18	q		9
19	r		0
Sheet1

Cell Formulas
Range	Formula
F2:F12	F2	{=IFERROR(LARGE(IF(OPEX_Summary_Desc<>"",IF(ABS(OPEX_Sum_Variance_vs_Bud)>=LARGE(IF(OPEX_Summary_Desc<>"",ABS(OPEX_Sum_Variance_vs_Bud)),10),OPEX_Sum_Variance_vs_Bud)),ROWS(F$2:F2)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Notice that this list actually has 11 entries in it, because there is a tie on the lowest absolute value (4).

 

[nrodrigues23]

This is exactly what I am looking for. Thank you so much. This is going to save me a boat load of time.

 

[Eric W]

Glad to help!