I have an excel table with this formula in each column cell; when a negative value (credit) is inserted in the [Goods Value] column the formula returns a #Num error?
=MROUND(SUM([@[Goods value]]*0.2),0.05)
Is there a way around this problem as it effects the subtotal at the bottom line as well.
Hi Frank
Give this a try
=MROUND(ABS(SUM([@[goods value]]*0.2)),0.05)*SIGN([@[goods value]])
Sunny Kow
Hi Frank,
This is a really good question!
The MROUND formula (understandably) wants to compare apples with apples.
For example, it needs MROUND(+ve,+ve) or MROUND(-ve,-ve) and that is why the #NUM error comes up when they don't match.
The good news is that there is a reasonably simple solution in the use of another handy function: "SIGN"
The SIGN function returns the sign of a number (+ or -) as a value:
+1 for a positive number
-1 for a negative number
0 for a zero
If we slot the SIGN function and a multiplier into the formula in your column here: =MROUND(SUM([@[Goods value]]*0.2), <SIGN *> 0.05) then we will be multiplying the 0.05 by 1 or -1 and thereby changing the value to match the sign of the [Goods Value] data - and make the MROUND happy 🙂
The final formula you need looks like this:
=MROUND(SUM([@[Goods Value]]*0.2),(SIGN([@[Goods Value]]))*0.05)
