Create a list of negative & positive values from a

问题:

I have the following Excel spreadsheet:

    A            B       C        D        E           F                 G    
1                 Q1     Q2       Q3      Q4      Negative values  Positive values
2   Asset 1      -50     85      -90      70         -10                 5 
3   Asset 2      -28    -80      -45      60         -15                27         
4   Asset 3      -30     50       55     -10         -20                50      
5   Asset 4      -20      5      -80     -15          :                 :  
6   Asset 5       35    -30       27     -98          :                 :
7                                                     :                 :

In Cells A1:E6 I have different assets with their performance from quarter Q1-Q4.

In Column F I want to have a list of all negative performances of the assets starting with the greates negative one (in this case -10).

In Column G I want to have a list of all positive performances of the assets starting with the lowest postive one (in this case 5).

Do you have any idea of a formula that could create these lists?

Note: All values are unique!

回答1:

Use LARGE and SMALL as Array formulas

negative:

=LARGE(IF($C$2:$F$6<0,C$2:$F$6),ROW(1:1))

and

Positive:

 =SMALL(IF($C$2:$F$6>0,C$2:$F$6),ROW(1:1))

Both are array formulas and need to confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.