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:

[Table="width:, class:grid"][tr][td] [/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

1​

[/td][td]

98​

[/td][td]

-99​

[/td][td]

99​

[/td][td][/td][td]C1: Input[/td][/tr]

[tr][td]

2​

[/td][td]

2​

[/td][td]

92​

[/td][td]

-98​

[/td][td]

98​

[/td][td][/td][td]D1: =LARGE($A$1:$A$34, C1)[/td][/tr]

[tr][td]

3​

[/td][td]

3​

[/td][td]

89​

[/td][td]

-95​

[/td][td]

95​

[/td][td][/td][td]E1: =SMALL($A$1:$A$34, C1)[/td][/tr]

[tr][td]

4​

[/td][td]

4​

[/td][td]

88​

[/td][td]

-86​

[/td][td]

88​

[/td][td][/td][td]F1: =IF(D1>-E1, D1, -E1)[/td][/tr]
[/table]

All formulas are confirmed normally.

 

[shg]

That's not right ...

 

[shg]

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

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

value​

[/td][td]

abs​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

-975​

[/td][td]

975​

[/td][td]B2: =ABS(A2)[/td][/tr]

[tr][td]

3​

[/td][td]

-970​

[/td][td]

970​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

962​

[/td][td]

962​

[/td][td][/td][/tr]

[tr][td]

5​

[/td][td]

-932​

[/td][td]

932​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

-897​

[/td][td]

897​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

880​

[/td][td]

880​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

-877​

[/td][td]

877​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

-868​

[/td][td]

868​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

868​

[/td][td]

868​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

-853​

[/td][td]

853​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

-819​

[/td][td]

819​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

786​

[/td][td]

786​

[/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

-773​

[/td][td]

773​

[/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

-735​

[/td][td]

735​

[/td][td][/td][/tr]

[tr][td]

16​

[/td][td]

-662​

[/td][td]

662​

[/td][td][/td][/tr]

[tr][td]

17​

[/td][td]

-636​

[/td][td]

636​

[/td][td][/td][/tr]

[tr][td]

18​

[/td][td]

-593​

[/td][td]

593​

[/td][td][/td][/tr]

[tr][td]

19​

[/td][td]

-554​

[/td][td]

554​

[/td][td][/td][/tr]

[tr][td]

20​

[/td][td]

-553​

[/td][td]

553​

[/td][td][/td][/tr]

[tr][td]

21​

[/td][td]

545​

[/td][td]

545​

[/td][td][/td][/tr]

[tr][td]

22​

[/td][td]

-518​

[/td][td]

518​

[/td][td][/td][/tr]

[tr][td]

23​

[/td][td]

504​

[/td][td]

504​

[/td][td][/td][/tr]

[tr][td]

24​

[/td][td]

488​

[/td][td]

488​

[/td][td][/td][/tr]

[tr][td]

25​

[/td][td]

-424​

[/td][td]

424​

[/td][td][/td][/tr]

[tr][td]

26​

[/td][td]

300​

[/td][td]

300​

[/td][td][/td][/tr]

[tr][td]

27​

[/td][td]

292​

[/td][td]

292​

[/td][td][/td][/tr]

[tr][td]

28​

[/td][td]

-262​

[/td][td]

262​

[/td][td][/td][/tr]

[tr][td]

29​

[/td][td]

236​

[/td][td]

236​

[/td][td][/td][/tr]

[tr][td]

30​

[/td][td]

-205​

[/td][td]

205​

[/td][td][/td][/tr]

[tr][td]

31​

[/td][td]

152​

[/td][td]

152​

[/td][td][/td][/tr]

[tr][td]

32​

[/td][td]

140​

[/td][td]

140​

[/td][td][/td][/tr]

[tr][td]

33​

[/td][td]

95​

[/td][td]

95​

[/td][td][/td][/tr]

[tr][td]

34​

[/td][td]

-85​

[/td][td]

85​

[/td][td][/td][/tr]

[tr][td]

35​

[/td][td]

15​

[/td][td]

15​

[/td][td][/td][/tr]
[/table]

 

[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.