Forum Discussion

pyajmal's avatar
pyajmal
Copper Contributor
Jun 27, 2018
Solved

How to find the maximum deviation of a set of number that contain negative values

Here I am attached the worksheet  
Formulas & Functions
training
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 27, 2018

    It could be

    ="Maximum deviation: " & TEXT(
   IF(ABS(MAX(AGGREGATE(14,6,C30:C35,1),AGGREGATE(14,6,F30:F35,1))) >
         ABS(MIN(AGGREGATE(15,6,C30:C35,1),AGGREGATE(15,6,F30:F35,1))),
      MAX(AGGREGATE(14,6,C30:C35,1),AGGREGATE(14,6,F30:F35,1)),
      MIN(AGGREGATE(15,6,C30:C35,1),AGGREGATE(15,6,F30:F35,1))
   ),
"0.000") & " bar"

    and attached

     

John Jairo Vergara Domínguez's avatar
John Jairo Vergara Domínguez
Brass Contributor
Jun 27, 2018

I think could be shorter:

 

="Maximum deviation: "&TEXT(AGGREGATE(14+(SUM(AGGREGATE({14;15},6,(C30:C35,F30:F35),1))<0),6,(C30:C35,F30:F35),1),"0.000")&" bar"

 

Blessings!

pyajmal's avatar
pyajmal
Copper Contributor
Jun 28, 2018

Thanks for the replay 

One problem is arise 

Here I am attached my worksheet 

Please go through it 

Resources