 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / September 2006Rounding of amounts
I have a small retail store and am currently working on price changes. I have an excel with all the calculated prices however I would like to round all amounts to xx.x9. The 2nd decimal should always be 9. Is there a way to do this in Excel? I checked the help file for anything related to rounding but did not find any examples that would let me do the rounding to 9. Thanks for any help. Gerd Try this: =CEILING(A1+0.01,0.1)-0.01  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > I have a small retail store and am currently working on price changes. I > have an excel with all the calculated prices however I would like to round [quoted text clipped - 4 lines] > Thanks for any help. > Gerd Thank you very much, RD, this works great. > Try this: > [quoted text clipped - 10 lines] >> Thanks for any help. >> Gerd Thanks for the feed-back. SignatureRegards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- Thank you very much, RD, this works great. > Try this: > [quoted text clipped - 10 lines] >> Thanks for any help. >> Gerd I got one more question for you. If my base price is already (lets say $2.79) your calculation rule adds another $ .10 to the price making the result $2.89 instead. I > Thanks for the feed-back. > Thank you very much, RD, this works great. [quoted text clipped - 18 lines] >>> Thanks for any help. >>> Gerd No it doesn't! Check it out again. *THIS* is what I posted: =CEILING(A1+0.01,0.1)-0.01 To get what you describe, you probably used this: =CEILING(A1+0.1,0.1)-0.01 SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- >I got one more question for you. If my base price is already (lets say >$2.79) your calculation rule adds another $ .10 to the price making the [quoted text clipped - 26 lines] >>>> Thanks for any help. >>>> Gerd Hi RD, In one of my columns I use formula =((($G7/16)*(1+N$4))*N$6) to calculate the selling price in ounces, my 2nd column uses formula =CEILING(((($G7/16)*(1+T$4))*T$6)+0.01,0.1)-0.01 which calculates the rounded selling price in ounces. If it happens that the price per ounce ends with a 9 the rounded price adds $ .10 cents to the amount. Here is an example: G7 (cost in lbs) = $9.96, T4 (markup) = 210%, T6 (packaging size in ounces) = 1.5 > No it doesn't! > [quoted text clipped - 38 lines] >>>>> Thanks for any help. >>>>> Gerd Using your formula of: =((($G7/16)*(1+T$4))*T$6) AND, the numbers in your example: G7 (cost in lbs) = $9.96, T4 (markup) = 210%, T6 (packaging size in ounces) = 1.5 Your formula returns 2.894625 Where the total is *not* 2.89 2.894625 becomes 2.90, Which rounds to 2.99 It's your decision what to charge in such cases as this. What would you want to do with an amount of 2.899999 As opposed to an amount of 2.8900000 If you are willing to accept (use) *only* the first two decimal places as the basis for your calculations, you might try something like this: =CEILING(TRUNC(($G7/16*(1+T$4)*T$6),2)+0.01,0.1)-0.01 SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Hi RD, In one of my columns I use formula =((($G7/16)*(1+N$4))*N$6) to calculate the selling price in ounces, my 2nd column uses formula =CEILING(((($G7/16)*(1+T$4))*T$6)+0.01,0.1)-0.01 which calculates the rounded selling price in ounces. If it happens that the price per ounce ends with a 9 the rounded price adds $ .10 cents to the amount. Here is an example: G7 (cost in lbs) = $9.96, T4 (markup) = 210%, T6 (packaging size in ounces) = 1.5 > No it doesn't! > [quoted text clipped - 40 lines] >>>>> Thanks for any help. >>>>> Gerd You are absolutely correct. I only displayed 2 decimals for my cost, thus, "assuming" the amount was what I saw. And thanks for the additional formula. I guess I now have to make a decission about how I want to calculate and display my cost. Thanks again for your excellent help. Gerd > Using your formula of: > [quoted text clipped - 98 lines] >>>>>> Thanks for any help. >>>>>> Gerd You're quite welcome, and thank you for the feed-back. SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > You are absolutely correct. I only displayed 2 decimals for my cost, thus, > "assuming" the amount was what I saw. [quoted text clipped - 108 lines] >>>>>>> Thanks for any help. >>>>>>> Gerd Gerd, Assuming that as you are selling you want to round up to the next x.x9, does: =ROUND([your price change formula]*10,0)/10+0.09 do what you want? SignatureHTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk >I have a small retail store and am currently working on price changes. I >have an excel with all the calculated prices however I would like to round [quoted text clipped - 4 lines] > Thanks for any help. > Gerd Thank you very much, Sandy. Your formula works for most of my columns but for 1. It must have something to do with the price formula that I use to calculate my new price. > Gerd, > [quoted text clipped - 13 lines] >> Thanks for any help. >> Gerd > Thank you very much, Sandy. Your formula works for most of my columns but > for 1. It must have something to do with the price formula that I use to > calculate my new price. I think that it is more likely the dumb formula I suggested - RD's formula is the way to go. SignatureRegards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace@mailinator.com with @tiscali.co.uk |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.