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