Need PC-Computer person, please

1. 85

   DzyMsLizzyposted 12 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.

   replyreport
   1. 74

      Quilligrapherposted 12 years agoin reply to this

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

      replyreport
   2. 87

      Marisa Wrightposted 12 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.

      replyreport
2. 84

   psycheskinnerposted 12 years ago

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

   replyreport
3. 80

   Aficionadaposted 12 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.

   replyreport
   1. 85

      DzyMsLizzyposted 12 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!

      replyreport
4. 84

   psycheskinnerposted 12 years ago

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

   replyreport
5. 80

   Aficionadaposted 12 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.

   replyreport