Sign Switch Logic and Subtotal

Hello Excel Friends,

I believe the logic is correct in my calculations, but I'm having a sign switch problem when I use Subtotal() to sum up a column. I have attached a file that demonstrates the problem. I outline what I am doing in the file.

I perform calculations on raw data, then I use =I5/ABS(I$2)*ABS(C$2) to determine what percentage each cell is of a total value. I use ABS() on the two total cells in that formula to make sure no signs cancel out - meaning I only want the sign of the I5 cell in the result. (The signs of I2 and C2 can flip depending on the data.)

That all seems to work perfectly for each row. However, when I sum up these result cells using SUBTOTAL(9,...) the resulting sum can have the sign switched even though it still equals the correct total.

The attached file outlines the issue.

Any help is greatly appreciated,
Ben

Excel Forum Test File.xlsx

The formula doesn't indicate an absolutely value for row 2.

Negative (* ÷) positive (* ÷) positive = negative

I don't really understand what you're asking, or trying to do.

But I can say with confidence that this formula
=SUBTOTAL(9,L5:L18)
Returns a negative number because the values in L5:L18 indeed add up to a negative number (-10.95)

Hi daffodil11,

The Absolute value formula is below in each cell (Rows 5 - 14). Row 2 just sums these up using SUBTOTAL().

Sorry, I meant the first value in the formula

=I5/ABS(I$2)*ABS(C$2)

I5 is not absolute, therefore if I5 is a negative, the result is negative.

If you want positive values, =abs(I5)/ABS(I$2)*ABS(C$2) would have to be your formula.

Originally Posted by daffodil11

If you want positive values, =abs(I5)/ABS(I$2)*ABS(C$2) would have to be your formula.

Or
=ABS(I5/I$2*C$2)

Hi Daffodil11,

Yes, I would agree with that, but I want negatives too, it just depends on the data. It may be too complicated to explain on here.

Thanks anyways,

The first ABS in the equation is causing the discrepancy.

=I5/I$2*(C$2) should be your formula. ABS(I$2) is causing the sign flip.

I tested it and everything matches if you remove this.