MROUND on a negative number.

Big Rick · 12-29-2005, 09:10 AM

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

Roger Govier · 12-29-2005, 09:15 AM

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.

Big Rick · 12-29-2005, 09:35 AM

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

Bob Phillips · 12-29-2005, 10:10 AM

=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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> >

Big Rick · 12-29-2005, 10:20 AM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> > >
>
>
>

Big Rick · 12-29-2005, 10:30 AM

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > > >
> >
> >
> >

Bob Phillips · 12-29-2005, 11:50 AM

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

Bob

"Big Rick" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > > >
> > >
> > >
> > >