MROUND on a negative number.

[Guest]

I came up with the following formula all by myself !!
=IF($I$2="NOT TRANSFERRED",$K$21+$K$22,MROUND($K$21+$K$22-$K$23-$K$24,0.25))
Which will successfully (If $I$2 = "TRANSFERRED") round a decimal number to
the nearest quarter.
If however, the decimal number is negative, I get the NUM error. Please can
you help me get it to return a negative rounded number.
E.g.
K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct at 15.75

but if
K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the result to be -0.5.

Your help is and always has been very much appreciated.
Thanking you in anticipation.

 

[Roger Govier]

Hi Rick

Just wrap the summation in an ABS() function
=IF($I$2="NOT TRANSFERRED",$K$21+$K$22,
MROUND(ABS($K$21+$K$22-$K$23-$K$24),0.25))

Regards

Roger Govier

 

[Guest]

Thanks, but not quite right.
The result of the ABS gives 0.5 while I require it to to -0.5
Please can you help me a little bit further.

Thank you

 

[Bob Phillips]

=IF($I$2="NOT
TRANSFERRED",$K$21+$K$22,ROUND(($K$21+$K$22-$K$23-$K$24)*4,0)/4)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Many many many thanks. If I am ever 1/3 (rounded up to 1/2) as good as you, I
will be a very happy man.
All the best in the forthingcoming year.

Regards

 

[Guest]

I dont know where 'forthingcoming' came from.
All the best in the forthcoming year.

 

[Bob Phillips]

I didn't even see it, I read what you meant )

Bob