+ Reply to Thread
Results 1 to 7 of 7

MROUND on a negative number.

  1. #1
    Big Rick
    Guest

    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

  2. #2
    Roger Govier
    Guest

    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.


  3. #3
    Big Rick
    Guest

    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.

    >


  4. #4
    Bob Phillips
    Guest

    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.

    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.

    > >




  5. #5
    Big Rick
    Guest

    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.

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

    >
    >
    >


  6. #6
    Big Rick
    Guest

    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.

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

    > >
    > >
    > >


  7. #7
    Bob Phillips
    Guest

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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1