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

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 28, 2018

    I took as the basis the formula which John suggested, it more advanced. If add "plus minus" when

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

    and attached

Resources