1

I have the following Excel spreadsheet:

     A            B       C        D      E                F                   G    
1                 Q1     Q2       Q3      Q4    Nearest negative value to 0   -10 
2   Asset 1      -50     85      -90      70    Nearest positive value to 0     5    
3   Asset 2      -28    -80      -45      60          
4   Asset 3      -30     50       55     -10                              
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.
The performance can be positive or negative.

In cell G1 and G2 I want to find the nearest negatvie and positive value to 0 in Cells B2:E6.
In this case this would -10 and 5. Therefore, I tried to go with the solution here:

G1 = {MAX(IF($B$2:$E$6<0,1,0)*($B$2:$E$6))}
G2 = {MIN(IF($B$2:$E$6>0,1,0)*($B$2:$E$6))}

However, both formulas give me 0 as a result.

Do you have any idea how to solve this issue?

NOTE: All values in Cells B2:E6 are unique.

Michi
  • 4,663
  • 6
  • 33
  • 83

2 Answers2

3

For largest negative number

=AGGREGATE(15,6,B2:E6/(B2:E6>0),1)

For smallest positive number

=AGGREGATE(14,6,B2:E6/(B2:E6<0),1)
Forward Ed
  • 9,484
  • 3
  • 22
  • 52
1

You do not need the *:

=MAX(IF($B$2:$E$6<0,$B$2:$E$6))

And 

=MIN(IF($B$2:$E$6>0,$B$2:$E$6))

These are Array formula and need to be confirmed with Ctrl-Shift-Enter.

Along with the AGGREGATE given by Forward Ed if on has Office 365 Excel:

=MAXIFS($B$2:$E$6,$B$2:$E$6,"<"0)

And

=MINIFS($B$2:$E$6,$B$2:$E$6,">"0)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81