| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
| How to make the cell or font color red if the number is negative? | Excel Worksheet Functions | |||
| ### when the number is NOT negative? | Excel Worksheet Functions | |||
| How do you enter a negative time number in Excel? | Excel Discussion (Misc queries) | |||
| 2003= negative number&2004= negative number How Do I Calculate gro | Excel Worksheet Functions | |||
Linear Mode
