# MROUND on a negative number.

1. ## MROUND on a negative number.

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.
--
Big Rick  Register To Reply

2. ## Re: MROUND on a negative number.

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

Big Rick wrote:
> 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.  Register To Reply

3. ## Re: MROUND on a negative number.

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
--
Big Rick

"Roger Govier" wrote:

> 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
>
>
> Big Rick wrote:
> > 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.

>  Register To Reply

4. ## Re: MROUND on a negative number.

=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)

"Big Rick" <BigRick@discussions.microsoft.com> wrote in message
news:97B69BFB-1B86-425C-A884-2171F10A2BF2@microsoft.com...
> 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
> --
> Big Rick
>
>
> "Roger Govier" wrote:
>
> > 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
> >
> >
> > Big Rick wrote:
> > > 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.

> > > 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.

> >  Register To Reply

5. ## Re: MROUND on a negative number.

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
--
Big Rick

"Bob Phillips" wrote:

> =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)
>
>
> "Big Rick" <BigRick@discussions.microsoft.com> wrote in message
> news:97B69BFB-1B86-425C-A884-2171F10A2BF2@microsoft.com...
> > 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
> > --
> > Big Rick
> >
> >
> > "Roger Govier" wrote:
> >
> > > 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
> > >
> > >
> > > Big Rick wrote:
> > > > 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.

> > > > 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.
> > >

>
>
>  Register To Reply

6. ## Re: MROUND on a negative number.

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

"Big Rick" wrote:

> 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
> --
> Big Rick
>
>
> "Bob Phillips" wrote:
>
> > =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)
> >
> >
> > "Big Rick" <BigRick@discussions.microsoft.com> wrote in message
> > news:97B69BFB-1B86-425C-A884-2171F10A2BF2@microsoft.com...
> > > 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
> > > --
> > > Big Rick
> > >
> > >
> > > "Roger Govier" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > Big Rick wrote:
> > > > > 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.

> > > > > 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.
> > > >

> >
> >
> >  Register To Reply

7. ## Re: MROUND on a negative number.

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

Bob

"Big Rick" <BigRick@discussions.microsoft.com> wrote in message
news:D2EDCC1B-B550-414C-A752-5C3B37C46A3A@microsoft.com...
> I dont know where 'forthingcoming' came from.
> All the best in the forthcoming year.
> --
> Big Rick
>
>
> "Big Rick" wrote:
>
> > 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
> > --
> > Big Rick
> >
> >
> > "Bob Phillips" wrote:
> >
> > > =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)
> > >
> > >
> > > "Big Rick" <BigRick@discussions.microsoft.com> wrote in message
> > > news:97B69BFB-1B86-425C-A884-2171F10A2BF2@microsoft.com...
> > > > 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
> > > > --
> > > > Big Rick
> > > >
> > > >
> > > > "Roger Govier" wrote:
> > > >
> > > > > 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
> > > > >
> > > > >
> > > > > Big Rick wrote:
> > > > > > 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.
> > > > > > 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.
> > > > >
> > >
> > >
> > >  Register To Reply