|
help with round function
i would like to use the round function so that if the 1st
decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? |
If you mean greater than or *equal* to .6, try:
=IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) HTH Jason Atlanta, GA -----Original Message----- i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? . |
=IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1)
You don't actually need the *1 because the addition of the INT and MOD functions will convert the Boolean. =IF(MOD(A1,1)=0.5,A1,ROUND(A1,0)) will also work Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Jason Morin" wrote in message ... If you mean greater than or *equal* to .6, try: =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) HTH Jason Atlanta, GA -----Original Message----- i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? . |
For some reason I have to multiply my boolean values by 1
in order sum them. I haven't figured out why. Jason -----Original Message----- =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) You don't actually need the *1 because the addition of the INT and MOD functions will convert the Boolean. =IF(MOD(A1,1)=0.5,A1,ROUND(A1,0)) will also work Regards Sandy -- to e-mail direct replace @mailinator.com with @tiscali.co.uk "Jason Morin" wrote in message ... If you mean greater than or *equal* to .6, try: =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) HTH Jason Atlanta, GA -----Original Message----- i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? . . |
If you are using the SUM function rather than addition operators, you will
note from Help that SUM ignores True/False values. On Tue, 8 Feb 2005 15:05:28 -0800, "Jason Morin" wrote: For some reason I have to multiply my boolean values by 1 in order sum them. I haven't figured out why. Jason -----Original Message----- =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) You don't actually need the *1 because the addition of the INT and MOD functions will convert the Boolean. =IF(MOD(A1,1)=0.5,A1,ROUND(A1,0)) will also work Regards Sandy -- to e-mail direct replace @mailinator.com with "Jason Morin" wrote in message ... If you mean greater than or *equal* to .6, try: =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) HTH Jason Atlanta, GA -----Original Message----- i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? . . |
Thanks Myrna, but if you look at my formula, I'm not
using SUM. -----Original Message----- If you are using the SUM function rather than addition operators, you will note from Help that SUM ignores True/False values. On Tue, 8 Feb 2005 15:05:28 -0800, "Jason Morin" wrote: For some reason I have to multiply my boolean values by 1 in order sum them. I haven't figured out why. Jason -----Original Message----- =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) You don't actually need the *1 because the addition of the INT and MOD functions will convert the Boolean. =IF(MOD(A1,1)=0.5,A1,ROUND(A1,0)) will also work Regards Sandy -- to e-mail direct replace @mailinator.com with "Jason Morin" wrote in message .. . If you mean greater than or *equal* to .6, try: =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) HTH Jason Atlanta, GA -----Original Message----- i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? . . . |
Your criteria are not clear. What do you want .599999999 to return?
What about .499999999? What about .400000001? Do you care that if you round .54 to .5 but .46 to .4 that you will be introducing bias into your rounded values? Jerry Scott wrote: i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? |
I was responding to this statement you made, not to your formula: "For some
reason I have to multiply my boolean values by 1 in order sum them. I haven't figured out why.". I don't know why you said that, since you aren't using SUM. On Tue, 8 Feb 2005 18:09:29 -0800, "Jason Morin" wrote: Thanks Myrna, but if you look at my formula, I'm not using SUM. -----Original Message----- If you are using the SUM function rather than addition operators, you will note from Help that SUM ignores True/False values. On Tue, 8 Feb 2005 15:05:28 -0800, "Jason Morin" wrote: For some reason I have to multiply my boolean values by 1 in order sum them. I haven't figured out why. Jason -----Original Message----- =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) You don't actually need the *1 because the addition of the INT and MOD functions will convert the Boolean. =IF(MOD(A1,1)=0.5,A1,ROUND(A1,0)) will also work Regards Sandy -- to e-mail direct replace @mailinator.com with "Jason Morin" wrote in message . .. If you mean greater than or *equal* to .6, try: =IF(MOD(A1,1)=0.5,A1,INT(A1)+(MOD(A1,1)=0.6)*1) HTH Jason Atlanta, GA -----Original Message----- i would like to use the round function so that if the 1st decimal place is .6 it rounds up and <.4 it rounds down, but does nothing if =.5 can this be done? . . . |
| All times are GMT +1. The time now is 12:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com