Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
1. Is it possible to do this excel?
Hi friends, i have a small query if you got little time. i have many numbers
in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
Try this
=FLOOR(A1,0.05) Mike "san" wrote: Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
Hi,
try =FLOOR(A3,0.05) "san" wrote: Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
=ROUNDDOWN(A1*20,)/20
"san" wrote: Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#5
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
1. Is it possible to do this excel?
Hi,
Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
Hi,
=IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) "san" wrote: Hi, Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
Hi,
disgegard my last formula, the one sent before works for me, "Eduardo" wrote: Hi, =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) "san" wrote: Hi, Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
=IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05))
Excel will never calculate a result of 219.40. It will calculate 219.4. The only way to get the terminating 0 is to use formatting and formatting is for display purposes only. Using that formula the only time RIGHT(A3,1)=0 will be TRUE is if you first correct it to read: RIGHT(A3,1)="0" If 219.40 is being rounded down to 219.35 then 219.40 is not the true underlying value of the cell. It might be something like 219.399999999999 but, due to formatting, is displaying as 219.40. So, the result of the formula, =FLOOR(G1,0.05) = 219.35, is correct. -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) "san" wrote: Hi, Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#9
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
In addition:
Using that formula the only time RIGHT(A3,1)=0 will be TRUE is if you first correct it to read: RIGHT(A3,1)="0" And, will only be TRUE when the number is an integer in a multiple of 10. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) Excel will never calculate a result of 219.40. It will calculate 219.4. The only way to get the terminating 0 is to use formatting and formatting is for display purposes only. Using that formula the only time RIGHT(A3,1)=0 will be TRUE is if you first correct it to read: RIGHT(A3,1)="0" If 219.40 is being rounded down to 219.35 then 219.40 is not the true underlying value of the cell. It might be something like 219.399999999999 but, due to formatting, is displaying as 219.40. So, the result of the formula, =FLOOR(G1,0.05) = 219.35, is correct. -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) "san" wrote: Hi, Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#10
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
If 219.40 is being rounded down to 219.35 then 219.40 is not the true
underlying value of the cell. It might be something like 219.399999999999 but, due to formatting, is displaying as 219.40. So, the result of the formula, =FLOOR(G1,0.05) = 219.35, is correct. Thanks, I finally understood it. The cell 219.198 was shown as 219.20. Thats why it when I used =FLOOR(G1,0.05) it showed me 219.15 which is correct. Thanks so much again. San "T. Valko" wrote in message ... =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) Excel will never calculate a result of 219.40. It will calculate 219.4. The only way to get the terminating 0 is to use formatting and formatting is for display purposes only. Using that formula the only time RIGHT(A3,1)=0 will be TRUE is if you first correct it to read: RIGHT(A3,1)="0" If 219.40 is being rounded down to 219.35 then 219.40 is not the true underlying value of the cell. It might be something like 219.399999999999 but, due to formatting, is displaying as 219.40. So, the result of the formula, =FLOOR(G1,0.05) = 219.35, is correct. -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) "san" wrote: Hi, Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#11
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
1. Is it possible to do this excel?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "san" wrote in message ... If 219.40 is being rounded down to 219.35 then 219.40 is not the true underlying value of the cell. It might be something like 219.399999999999 but, due to formatting, is displaying as 219.40. So, the result of the formula, =FLOOR(G1,0.05) = 219.35, is correct. Thanks, I finally understood it. The cell 219.198 was shown as 219.20. Thats why it when I used =FLOOR(G1,0.05) it showed me 219.15 which is correct. Thanks so much again. San "T. Valko" wrote in message ... =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) Excel will never calculate a result of 219.40. It will calculate 219.4. The only way to get the terminating 0 is to use formatting and formatting is for display purposes only. Using that formula the only time RIGHT(A3,1)=0 will be TRUE is if you first correct it to read: RIGHT(A3,1)="0" If 219.40 is being rounded down to 219.35 then 219.40 is not the true underlying value of the cell. It might be something like 219.399999999999 but, due to formatting, is displaying as 219.40. So, the result of the formula, =FLOOR(G1,0.05) = 219.35, is correct. -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, =IF(RIGHT(A3,1)=0,A3,FLOOR(A3,0.05)) "san" wrote: Hi, Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San "san" wrote in message .. . Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. 219.39---219.35 219.32---219.30 219.34---219.30 219.37---219.35 I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. |
#12
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
1. Is it possible to do this excel?
Bonsour® san avec ferveur ;o))) vous nous disiez :
Thanks so much for such a quick reply. I tried your function =FLOOR(G1,0.05) on my worksheet, but on few cells it gave me result like this 219.40---219.35 which should remain 219.40 219.55---219.50 which should remain 219.55 I wonder why it happened. Please help me on this too. Thanks. San Hi friends, i have a small query if you got little time. i have many numbers in an excel worksheet and i want to round them up only for decimal places and that too like this way. I mean decimal places must end in .05, .10, .15, .20, .25, .30, so on. what is the function for this result. thanks in advance. Rounding to the nearest 0.05 : =0.05 * (INT((A1+ 0.025 ) / 0.05 )) HTH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|