Forum Discussion
Solved
How to find the maximum deviation of a set of number that contain negative values
Here I am attached the worksheet
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
- 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.
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
Thanks sir ,
Here I am attached my worksheet
another problem is Highlighted in the worksheet
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
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!
Thanks for the replay
One problem is arise
Here I am attached my worksheet
Please go through it
