I was recently working with some data that had some issues with the sign on the quantities. The quantities should have had the same sign as the associated dollar amount, but they didn’t. Here is how I fixed it.

The table below has the type of issue faced. The Amount column should, and does, balance to zero. The Qty column doesn’t.

Here is the solution.

The formula in cell C2 that has been copied down is.

=IF(B2<=0,-ABS(A2),ABS(A2))

The ABS function removes negatives from negative values, whilst leaving positive numbers unchanged. This formula makes sure that negative amounts have a corresponding negative quantity. Positive amounts have a positive quantity.

Using the ABS removes the need to check what sign the existing quantity has. ABS converts all numbers to positives so we can then easily control the sign.