Need PC-Computer person, please

Jump to Last Post 1-5 of 5 discussions (8 posts)
  1. DzyMsLizzy profile image95
    DzyMsLizzyposted 7 years ago

    Hi-
      I'm not a techie, and I'm not a bookkeeper.  However, I do need to use Excel for various functions in keeping track of things like inventory and price sheets.

      I am able to do that fairly proficiently.  However, I do have a specific function I need to do, and I don't know if it's possible or not.  That is a "round the number to..." function.

      I've found some tips online for rounding to a specific number of decimal places (up/down) but that is not quite what I need.

      First, I'm working with an older version, (Excel 2000, to be exact) that I cannot afford to update.

       What I have done is create forumlas to determine the price to be charged for a given item, based on various divisors and multipliers.  That much I was able to do on my own.

      However, what I need now, (if it is possible) is to have a formula that will tell Excel to "take the price in this column, and round it to the nearest nickel.  That could be either a roundin up OR a rounding down.

      Can this be done?  If so, what is the formula I need?  The current formula reads:  =E2*0.6  I need to be able to add to this a command for 'round to nearest nickel.'

    Thanks much to anyone who can either provide a forumla, or let me know if it is impossible.

    1. Quilligrapher profile image84
      Quilligrapherposted 7 years agoin reply to this

      ROUND((+E2*0.6)*20, 0)/20

    2. Marisa Wright profile image95
      Marisa Wrightposted 7 years agoin reply to this

      This may be too obvious, but if all you want to do is show the number to the nearest nickel in the spreadsheet, then you don't need any fancy rounding formula.  You just need to tell the spreadsheet to only show 2 decimal places.

  2. psycheskinner profile image82
    psycheskinnerposted 7 years ago

    I agree, the simplest option would be to select the sheet, then format the cells to show two decimal places.

  3. Aficionada profile image83
    Aficionadaposted 7 years ago

    Of course, two decimal places could also be to the nearest penny.

    I would think that rounding to the nearest dime would be easier (allow only one decimal place), but that wouldn't necessarily be what DzyMsLizzy needs.

    1. DzyMsLizzy profile image95
      DzyMsLizzyposted 7 years agoin reply to this

      Thank you, Quilligrapher,  Marisa, psycheskinner and Afficionada.

      In fact, this is for an updated price list for our new product catalog, and we don't want to go to 'nearest dime' because we are trying to avoid much of a price increase or decrease (since our main competition is cheaply-made Chinese imports)...so the nearest nickel is our choice.

        I currently have it set for 2 decimal places, and some prices are to the nearest penny...$1.84 or $2.96, etc.
      I want even nickels for ease of calculating.  and basically holding the line on prices.   ...   ;-)

      I will try everyone's suggestions--thank you all so much!

  4. psycheskinner profile image82
    psycheskinnerposted 7 years ago

    Yes, I misread that--so to one DP.

  5. Aficionada profile image83
    Aficionadaposted 7 years ago

    DzyMsLizzy, I really love Excel, and I use it for a lot of things too, so I just went into it to see what I could find out.  There actually is a way to do what you want, but it may be necessary for you to add the "Analysis ToolPak add-in."  (I do not have that on my computer, and I don't intend to add it.)

    I use Excel 2003, which I thought might just be close enough to your version to be helpful.  What I tried did not work (I'll explain it, though), and the Help section said "If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in." - Well, for me, it did return the #NAME? error, so I guess I would have to install the add-in, but I'm not likely to.  Anyway, here's what you should try, in a couple of different versions, since I'm not sure what actually works!

    Click on the cell where you want the answer to appear; then type in the following:

    =MROUND(Num, 0.05)

    - only, instead of Num, type the name of the cell where the original value is housed. [For example, =MROUND(E5, 0.05)] If that doesn't work, but also doesn't return the #NAME? error, try

    =MROUND(Num, 5)

    - I think the first version would be correct, because I believe this one would indicate a multiple of the whole number 5, instead of a multiple of 0.05.

    [MROUND refers to rounding to a multiple of....]

    Of course, you should be able to use the "fill-down" or "fill-right" option to work for many cells in a column or row, so that you don't have to type this more than once. I would be very interested to learn whether this works for you.  Please let me know! - PS, If it doesn't, try an offline search for MROUND.  I can hardly ever find anything with an online search for Microsoft Office programs.

 
working

This website uses cookies

As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

Show Details
Necessary
HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
LoginThis is necessary to sign in to the HubPages Service.
Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
AkismetThis is used to detect comment spam. (Privacy Policy)
HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
Features
Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
MavenThis supports the Maven widget and search functionality. (Privacy Policy)
Marketing
Google AdSenseThis is an ad network. (Privacy Policy)
Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
Index ExchangeThis is an ad network. (Privacy Policy)
SovrnThis is an ad network. (Privacy Policy)
Facebook AdsThis is an ad network. (Privacy Policy)
Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
AppNexusThis is an ad network. (Privacy Policy)
OpenxThis is an ad network. (Privacy Policy)
Rubicon ProjectThis is an ad network. (Privacy Policy)
TripleLiftThis is an ad network. (Privacy Policy)
Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
Statistics
Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)