Using the ABS Function with SUM, AVERAGE and MAX as an Array Formula
The video below takes the following scenario:
We have a list of test results that contain negative and positive numbers. We want to do some calculations on the results, using the SUM, AVERAGE, MAX and MIN functions. All numbers need to be treated as positive to get the results we want.
The ABS function solves part of the problem as it removes the negative sign by returning the absolute version of the number. ABS however doesn’t work on ranges, so we can’t say for example =SUM(ABS(A1:A10)) as we get the #VALUE error. This is where we need an array formula, using CTRL SHIFT ENTER to confirm our formula, creating the following {=SUM(ABS(A1:A10))} (notice the brace brackets).
An array formula allows us to perform calculations on a range of cells where it isn’t normally possible.