software

I came up with the following formula all by myself !!
=IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
Which will successfully (If $I$2 = quot;TRANSFERREDquot;) 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

Hi Rick

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

Regards

Roger GovierBig Rick wrote:
gt; I came up with the following formula all by myself !!
gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
gt; Which will successfully (If $I$2 = quot;TRANSFERREDquot;) round a decimal number to
gt; the nearest quarter.
gt; If however, the decimal number is negative, I get the NUM error. Please can
gt; you help me get it to return a negative rounded number.
gt; E.g.
gt; K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct at 15.75
gt;
gt; but if
gt; K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the result to be -0.5.
gt;
gt; Your help is and always has been very much appreciated.
gt; Thanking you in anticipation.

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 Rickquot;Roger Govierquot; wrote:

gt; Hi Rick
gt;
gt; Just wrap the summation in an ABS() function
gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,
gt; MROUND(ABS($K$21 $K$22-$K$23-$K$24),0.25))
gt;
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; Big Rick wrote:
gt; gt; I came up with the following formula all by myself !!
gt; gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
gt; gt; Which will successfully (If $I$2 = quot;TRANSFERREDquot;) round a decimal number to
gt; gt; the nearest quarter.
gt; gt; If however, the decimal number is negative, I get the NUM error. Please can
gt; gt; you help me get it to return a negative rounded number.
gt; gt; E.g.
gt; gt; K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct at 15.75
gt; gt;
gt; gt; but if
gt; gt; K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the result to be -0.5.
gt; gt;
gt; gt; Your help is and always has been very much appreciated.
gt; gt; Thanking you in anticipation.
gt;

=IF($I$2=quot;NOT
TRANSFERREDquot;,$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)quot;Big Rickquot; gt; wrote in message
...
gt; Thanks, but not quite right.
gt; The result of the ABS gives 0.5 while I require it to to -0.5
gt; Please can you help me a little bit further.
gt;
gt; Thank you
gt; --
gt; Big Rick
gt;
gt;
gt; quot;Roger Govierquot; wrote:
gt;
gt; gt; Hi Rick
gt; gt;
gt; gt; Just wrap the summation in an ABS() function
gt; gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,
gt; gt; MROUND(ABS($K$21 $K$22-$K$23-$K$24),0.25))
gt; gt;
gt; gt; Regards
gt; gt;
gt; gt; Roger Govier
gt; gt;
gt; gt;
gt; gt; Big Rick wrote:
gt; gt; gt; I came up with the following formula all by myself !!
gt; gt; gt; =IF($I$2=quot;NOT
TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
gt; gt; gt; Which will successfully (If $I$2 = quot;TRANSFERREDquot;) round a decimal
number to
gt; gt; gt; the nearest quarter.
gt; gt; gt; If however, the decimal number is negative, I get the NUM error.
Please can
gt; gt; gt; you help me get it to return a negative rounded number.
gt; gt; gt; E.g.
gt; gt; gt; K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct at
15.75
gt; gt; gt;
gt; gt; gt; but if
gt; gt; gt; K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the result to
be -0.5.
gt; gt; gt;
gt; gt; gt; Your help is and always has been very much appreciated.
gt; gt; gt; Thanking you in anticipation.
gt; gt;
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 Rickquot;Bob Phillipsquot; wrote:

gt; =IF($I$2=quot;NOT
gt; TRANSFERREDquot;,$K$21 $K$22,ROUND(($K$21 $K$22-$K$23-$K$24)*4,0)/4)
gt;
gt; --
gt;
gt; HTH
gt;
gt; RP
gt; (remove nothere from the email address if mailing direct)
gt;
gt;
gt; quot;Big Rickquot; gt; wrote in message
gt; ...
gt; gt; Thanks, but not quite right.
gt; gt; The result of the ABS gives 0.5 while I require it to to -0.5
gt; gt; Please can you help me a little bit further.
gt; gt;
gt; gt; Thank you
gt; gt; --
gt; gt; Big Rick
gt; gt;
gt; gt;
gt; gt; quot;Roger Govierquot; wrote:
gt; gt;
gt; gt; gt; Hi Rick
gt; gt; gt;
gt; gt; gt; Just wrap the summation in an ABS() function
gt; gt; gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,
gt; gt; gt; MROUND(ABS($K$21 $K$22-$K$23-$K$24),0.25))
gt; gt; gt;
gt; gt; gt; Regards
gt; gt; gt;
gt; gt; gt; Roger Govier
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Big Rick wrote:
gt; gt; gt; gt; I came up with the following formula all by myself !!
gt; gt; gt; gt; =IF($I$2=quot;NOT
gt; TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
gt; gt; gt; gt; Which will successfully (If $I$2 = quot;TRANSFERREDquot;) round a decimal
gt; number to
gt; gt; gt; gt; the nearest quarter.
gt; gt; gt; gt; If however, the decimal number is negative, I get the NUM error.
gt; Please can
gt; gt; gt; gt; you help me get it to return a negative rounded number.
gt; gt; gt; gt; E.g.
gt; gt; gt; gt; K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct at
gt; 15.75
gt; gt; gt; gt;
gt; gt; gt; gt; but if
gt; gt; gt; gt; K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the result to
gt; be -0.5.
gt; gt; gt; gt;
gt; gt; gt; gt; Your help is and always has been very much appreciated.
gt; gt; gt; gt; Thanking you in anticipation.
gt; gt; gt;
gt;
gt;
gt;

I dont know where 'forthingcoming' came from.
All the best in the forthcoming year.
--
Big Rickquot;Big Rickquot; wrote:

gt; Many many many thanks. If I am ever 1/3 (rounded up to 1/2) as good as you, I
gt; will be a very happy man.
gt; All the best in the forthingcoming year.
gt;
gt; Regards
gt; --
gt; Big Rick
gt;
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; =IF($I$2=quot;NOT
gt; gt; TRANSFERREDquot;,$K$21 $K$22,ROUND(($K$21 $K$22-$K$23-$K$24)*4,0)/4)
gt; gt;
gt; gt; --
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; RP
gt; gt; (remove nothere from the email address if mailing direct)
gt; gt;
gt; gt;
gt; gt; quot;Big Rickquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Thanks, but not quite right.
gt; gt; gt; The result of the ABS gives 0.5 while I require it to to -0.5
gt; gt; gt; Please can you help me a little bit further.
gt; gt; gt;
gt; gt; gt; Thank you
gt; gt; gt; --
gt; gt; gt; Big Rick
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Roger Govierquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi Rick
gt; gt; gt; gt;
gt; gt; gt; gt; Just wrap the summation in an ABS() function
gt; gt; gt; gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,
gt; gt; gt; gt; MROUND(ABS($K$21 $K$22-$K$23-$K$24),0.25))
gt; gt; gt; gt;
gt; gt; gt; gt; Regards
gt; gt; gt; gt;
gt; gt; gt; gt; Roger Govier
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Big Rick wrote:
gt; gt; gt; gt; gt; I came up with the following formula all by myself !!
gt; gt; gt; gt; gt; =IF($I$2=quot;NOT
gt; gt; TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
gt; gt; gt; gt; gt; Which will successfully (If $I$2 = quot;TRANSFERREDquot;) round a decimal
gt; gt; number to
gt; gt; gt; gt; gt; the nearest quarter.
gt; gt; gt; gt; gt; If however, the decimal number is negative, I get the NUM error.
gt; gt; Please can
gt; gt; gt; gt; gt; you help me get it to return a negative rounded number.
gt; gt; gt; gt; gt; E.g.
gt; gt; gt; gt; gt; K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct at
gt; gt; 15.75
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; but if
gt; gt; gt; gt; gt; K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the result to
gt; gt; be -0.5.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Your help is and always has been very much appreciated.
gt; gt; gt; gt; gt; Thanking you in anticipation.
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;

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

Bobquot;Big Rickquot; gt; wrote in message
news
gt; I dont know where 'forthingcoming' came from.
gt; All the best in the forthcoming year.
gt; --
gt; Big Rick
gt;
gt;
gt; quot;Big Rickquot; wrote:
gt;
gt; gt; Many many many thanks. If I am ever 1/3 (rounded up to 1/2) as good as
you, I
gt; gt; will be a very happy man.
gt; gt; All the best in the forthingcoming year.
gt; gt;
gt; gt; Regards
gt; gt; --
gt; gt; Big Rick
gt; gt;
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; =IF($I$2=quot;NOT
gt; gt; gt; TRANSFERREDquot;,$K$21 $K$22,ROUND(($K$21 $K$22-$K$23-$K$24)*4,0)/4)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; RP
gt; gt; gt; (remove nothere from the email address if mailing direct)
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Big Rickquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Thanks, but not quite right.
gt; gt; gt; gt; The result of the ABS gives 0.5 while I require it to to -0.5
gt; gt; gt; gt; Please can you help me a little bit further.
gt; gt; gt; gt;
gt; gt; gt; gt; Thank you
gt; gt; gt; gt; --
gt; gt; gt; gt; Big Rick
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Roger Govierquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi Rick
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Just wrap the summation in an ABS() function
gt; gt; gt; gt; gt; =IF($I$2=quot;NOT TRANSFERREDquot;,$K$21 $K$22,
gt; gt; gt; gt; gt; MROUND(ABS($K$21 $K$22-$K$23-$K$24),0.25))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Regards
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Roger Govier
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Big Rick wrote:
gt; gt; gt; gt; gt; gt; I came up with the following formula all by myself !!
gt; gt; gt; gt; gt; gt; =IF($I$2=quot;NOT
gt; gt; gt; TRANSFERREDquot;,$K$21 $K$22,MROUND($K$21 $K$22-$K$23-$K$24,0.25))
gt; gt; gt; gt; gt; gt; Which will successfully (If $I$2 = quot;TRANSFERREDquot;) round a
decimal
gt; gt; gt; number to
gt; gt; gt; gt; gt; gt; the nearest quarter.
gt; gt; gt; gt; gt; gt; If however, the decimal number is negative, I get the NUM error.
gt; gt; gt; Please can
gt; gt; gt; gt; gt; gt; you help me get it to return a negative rounded number.
gt; gt; gt; gt; gt; gt; E.g.
gt; gt; gt; gt; gt; gt; K21 = 17.00 K22 = 0 K23 = 0 K24 = 1.28 Therefore correct
at
gt; gt; gt; 15.75
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; but if
gt; gt; gt; gt; gt; gt; K21 = 0 K22 = 0 K23 = 0.6 K24 = 0 I would like the
result to
gt; gt; gt; be -0.5.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Your help is and always has been very much appreciated.
gt; gt; gt; gt; gt; gt; Thanking you in anticipation.
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;