June 10, 2016
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?
Is there a way around this problem as it effects the subtotal at the bottom line as well.
June 25, 2016
July 13, 2016
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)
October 5, 2010
Read this post to learn more on the SIGN function
Most Users Ever Online: 57
Currently Browsing this Page:
Frans Visser: 210
mey tithveasna: 71
Anders Sehlstedt: 47
Guest Posters: 1
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas