Absolute with Large function

[corinnejacobson]

I have a column of numbers both positive and negative. I want to get a table of the 4 largest absolute values WITHOUT having to create another column of those numbers as absolute values.
I tried LARGE(abs(A1:A34),1) - then 2,3,4 - but it came back #NUM. Any thoughts?

 

[shg]

The formula needs to be confirmed by pressing and holding the Ctrl and Shift keys, then pressing Enter.

 

[corinnejacobson]

The formula needs to be confirmed by pressing and holding the Ctrl and Shift keys, then pressing Enter.

That works - except it pulls in the negative values as positives, I still want them to be negative when they populate the table.

 

[shg]

One way:

	C​	D​	E​	F​	G​	H​
1​	1​	98​	-99​	99​		C1: Input
2​	2​	92​	-98​	98​		D1: =LARGE($A$1:$A$34, C1)
3​	3​	89​	-95​	95​		E1: =SMALL($A$1:$A$34, C1)
4​	4​	88​	-86​	88​		F1: =IF(D1>-E1, D1, -E1)

All formulas are confirmed normally.

 

[shg]

That's not right ...

 

[shg]

I think the easiest way is to just sort by absolute value:

	A​	B​	C​
1​	value​	abs​
2​	-975​	975​	B2: =ABS(A2)
3​	-970​	970​
4​	962​	962​
5​	-932​	932​
6​	-897​	897​
7​	880​	880​
8​	-877​	877​
9​	-868​	868​
10​	868​	868​
11​	-853​	853​
12​	-819​	819​
13​	786​	786​
14​	-773​	773​
15​	-735​	735​
16​	-662​	662​
17​	-636​	636​
18​	-593​	593​
19​	-554​	554​
20​	-553​	553​
21​	545​	545​
22​	-518​	518​
23​	504​	504​
24​	488​	488​
25​	-424​	424​
26​	300​	300​
27​	292​	292​
28​	-262​	262​
29​	236​	236​
30​	-205​	205​
31​	152​	152​
32​	140​	140​
33​	95​	95​
34​	-85​	85​
35​	15​	15​

 

[HZRMike]

So I used an IF statement on mine. =IF(ABS(Large(A1:A34,2))>ABS(SMALL(A1:A34,2)),LARGE(A1:A34,2,SMALL(A1:A34,2))

It's a little clunky but it works.