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 |
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. |
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. |
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" wrote in message ... 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. |
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" wrote in message ... 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. |
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" wrote in message ... 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. |
MROUND on a negative number.
I didn't even see it, I read what you meant :-))
Bob "Big Rick" wrote in message ... 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" wrote in message ... 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. |
All times are GMT +1. The time now is 05:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com