Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
Want to know if this is even possible. I need excel to round numbers down
that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
Look at the ROUNDDOWN and ROUNDUP features
=IF(A1=.51,ROUNDUP(A1,0),ROUNDDOWN(A1,0)) Dave -- Brevity is the soul of wit. "UBR362" wrote: Want to know if this is even possible. I need excel to round numbers down that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
Dave
Here is what I want E9 = 150.00 (Base amount) G9 = 154.50 (Adjusted amount) I9 = 154.00 (Trying to get this to round down to 154 not 155) I plugged in what you gave me but it didn't work. Heres what I tried. =IF(G9=.51,ROUNDUP(G9,0),ROUNDDOWN(G9,0)) This gave me 155 which I need it to round down to 154. Thanks "Dave F" wrote: Look at the ROUNDDOWN and ROUNDUP features =IF(A1=.51,ROUNDUP(A1,0),ROUNDDOWN(A1,0)) Dave -- Brevity is the soul of wit. "UBR362" wrote: Want to know if this is even possible. I need excel to round numbers down that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
=IF(MOD(G9,1)=0.51,ROUNDUP(G9,0),ROUNDDOWN(G9,0))
-- --- HTH Bob (change the xxxx to gmail if mailing direct) "UBR362" wrote in message ... Dave Here is what I want E9 = 150.00 (Base amount) G9 = 154.50 (Adjusted amount) I9 = 154.00 (Trying to get this to round down to 154 not 155) I plugged in what you gave me but it didn't work. Heres what I tried. =IF(G9=.51,ROUNDUP(G9,0),ROUNDDOWN(G9,0)) This gave me 155 which I need it to round down to 154. Thanks "Dave F" wrote: Look at the ROUNDDOWN and ROUNDUP features =IF(A1=.51,ROUNDUP(A1,0),ROUNDDOWN(A1,0)) Dave -- Brevity is the soul of wit. "UBR362" wrote: Want to know if this is even possible. I need excel to round numbers down that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
Hi--Bob Phillips' response should address your issue.
From your original post, it sounded like the values in question were 0.50 and 0.51. Apologies. Dave -- Brevity is the soul of wit. "UBR362" wrote: Dave Here is what I want E9 = 150.00 (Base amount) G9 = 154.50 (Adjusted amount) I9 = 154.00 (Trying to get this to round down to 154 not 155) I plugged in what you gave me but it didn't work. Heres what I tried. =IF(G9=.51,ROUNDUP(G9,0),ROUNDDOWN(G9,0)) This gave me 155 which I need it to round down to 154. Thanks "Dave F" wrote: Look at the ROUNDDOWN and ROUNDUP features =IF(A1=.51,ROUNDUP(A1,0),ROUNDDOWN(A1,0)) Dave -- Brevity is the soul of wit. "UBR362" wrote: Want to know if this is even possible. I need excel to round numbers down that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
Sandy
The base amount is for me to keep track of changes. In the base amount column I input the adjusted amount year to year. So next year the base amount becomes what the adjusted amount was this year, and so forth and so on. I inputed the formula that you and Bob gave me and it seemed to work until I plugged in enough changes to the "base amount" that I ended up with a case where it was E9 = 183.99 ("Base Amount") G9 = 189.51 (Adjusted Amount) I9 = 189.00 (Rounded Amount) Is it a matter of just carrying out more decimal places? What I am seeing is that the G9 field is being rounded up to 189.51 (acutal is 189.5097). Would that affect the results of I9 at all? Thanks "Sandy Mann" wrote: I don't understand where the Base amount comes in but to round G9 to the value in I9 try: =IF(MOD(G9,1)0.5,ROUNDUP(G9,0),ROUNDDOWN(G9,0)) or =ROUND(G9-10^-10,0.5) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "UBR362" wrote in message ... Dave Here is what I want E9 = 150.00 (Base amount) G9 = 154.50 (Adjusted amount) I9 = 154.00 (Trying to get this to round down to 154 not 155) I plugged in what you gave me but it didn't work. Heres what I tried. =IF(G9=.51,ROUNDUP(G9,0),ROUNDDOWN(G9,0)) This gave me 155 which I need it to round down to 154. Thanks "Dave F" wrote: Look at the ROUNDDOWN and ROUNDUP features =IF(A1=.51,ROUNDUP(A1,0),ROUNDDOWN(A1,0)) Dave -- Brevity is the soul of wit. "UBR362" wrote: Want to know if this is even possible. I need excel to round numbers down that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
No aplogies necessary. As I said I'm inept at this. Thanks for your help.
"Dave F" wrote: Hi--Bob Phillips' response should address your issue. From your original post, it sounded like the values in question were 0.50 and 0.51. Apologies. Dave -- Brevity is the soul of wit. "UBR362" wrote: Dave Here is what I want E9 = 150.00 (Base amount) G9 = 154.50 (Adjusted amount) I9 = 154.00 (Trying to get this to round down to 154 not 155) I plugged in what you gave me but it didn't work. Heres what I tried. =IF(G9=.51,ROUNDUP(G9,0),ROUNDDOWN(G9,0)) This gave me 155 which I need it to round down to 154. Thanks "Dave F" wrote: Look at the ROUNDDOWN and ROUNDUP features =IF(A1=.51,ROUNDUP(A1,0),ROUNDDOWN(A1,0)) Dave -- Brevity is the soul of wit. "UBR362" wrote: Want to know if this is even possible. I need excel to round numbers down that are .50 and lower and up for numbers that are .51 and higher. Excel tends to round up when numbers are .50 and higher. So for instance 10.51 and higher would need to round up to 11 whereas 10.50 and lower would round down 10. I am very unfamiliar with formulas so if you can explain to me what to do step by step I would appreciate it. Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
UBR362 wrote:
I inputed the formula that [Sandy] and Bob gave me and it seemed to work until I plugged in enough changes to the "base amount" that I ended up with a case where it was E9 = 183.99 ("Base Amount") G9 = 189.51 (Adjusted Amount) I9 = 189.00 (Rounded Amount) And you wanted 190? The problem is in the ambiguity of your original posting. You said you wanted to round up for "10.51 and higher" and round down for "10.50 and lower". The question is: want about between 10.50 and 10.51? Bob focused on "10.51 and higher". Sandy interpreted you to mean "round up for any amount greater than 10.50". Apparently, Sandy's interpretation is closer to your intent. Use Sandy's formula, namely MOD(...) 0.50. However, I suspect you actually mean: round up if the __displayed__ value is greater than 10.50. For example, 10.505 (displayed 10.51) should round up, but 10.5049 (displayed 10.50) should round down. In that case, modify Sandy's formula as follows: =if(mod(round(G9,2),1) 0.50, roundup(G9,0), rounddown(G9,0)) Finally, I wonder if you are really trying to implement banker's rounding. You example rounded 10.50 down to 10.00. What about 11.50? Do you want 11.00 or 12.00? Is it a matter of just carrying out more decimal places? What I am seeing is that the G9 field is being rounded up to 189.51 (acutal is 189.5097). Would that affect the results of I9 at all? Yes, that is the problem. Also, with respect to Bob's v. Sandy's formula, keep in mind that (binary) computers can represent 0.50 exactly, but not 0.51. So " 0.50" is better than "= 0.51", even after you make the round(G9,2) change, which might otherwise seem to make the two formulas identical in effect. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Rounding in Excel
Ok my bad
The first example I used was just a random number I was using to "try" to explain what I want to do. Yes I did want 190 and thats where I guess my problem lies, it went down to 189 instead of rounding up to 190. Yeah I realize this is probably a confusing question but the whole thing was confusing to me so I guess I appreciate you guys helping me out here. I guess I was looking more at if its displayed in the g9 as .51 then why isn't it treating it like .51 instead of .5097. But there again my bad. I tried your mod(...) and it worked. Thank you very much everyone for your help and next time I will try to explain myself a little better. Merry Christmas to everyone (Happy Holidays for the PC people) and Have a good New Years. " wrote: UBR362 wrote: I inputed the formula that [Sandy] and Bob gave me and it seemed to work until I plugged in enough changes to the "base amount" that I ended up with a case where it was E9 = 183.99 ("Base Amount") G9 = 189.51 (Adjusted Amount) I9 = 189.00 (Rounded Amount) And you wanted 190? The problem is in the ambiguity of your original posting. You said you wanted to round up for "10.51 and higher" and round down for "10.50 and lower". The question is: want about between 10.50 and 10.51? Bob focused on "10.51 and higher". Sandy interpreted you to mean "round up for any amount greater than 10.50". Apparently, Sandy's interpretation is closer to your intent. Use Sandy's formula, namely MOD(...) 0.50. However, I suspect you actually mean: round up if the __displayed__ value is greater than 10.50. For example, 10.505 (displayed 10.51) should round up, but 10.5049 (displayed 10.50) should round down. In that case, modify Sandy's formula as follows: =if(mod(round(G9,2),1) 0.50, roundup(G9,0), rounddown(G9,0)) Finally, I wonder if you are really trying to implement banker's rounding. You example rounded 10.50 down to 10.00. What about 11.50? Do you want 11.00 or 12.00? Is it a matter of just carrying out more decimal places? What I am seeing is that the G9 field is being rounded up to 189.51 (acutal is 189.5097). Would that affect the results of I9 at all? Yes, that is the problem. Also, with respect to Bob's v. Sandy's formula, keep in mind that (binary) computers can represent 0.50 exactly, but not 0.51. So " 0.50" is better than "= 0.51", even after you make the round(G9,2) change, which might otherwise seem to make the two formulas identical in effect. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel won't stop rounding numbers. Please help | Excel Discussion (Misc queries) | |||
Excel file with hyperlinks takes a long time to open over the network | Links and Linking in Excel | |||
How do I stop excel from rounding numbers? | Excel Discussion (Misc queries) | |||
How do I record (.468) time in Excel without it rounding to .5? | Excel Discussion (Misc queries) |