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

     

cool2021's avatar
cool2021
Iron Contributor
Jul 30, 2022
Instead of going through all that trouble with a formula, you could just use a box plot graph template that, at the click of a button, will identify your high and low deviations without having to convert your negative values to absolutes.

Resources