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.
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.
I agree, the simplest option would be to select the sheet, then format the cells to show two decimal places.
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.
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!
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:
- 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
- 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.
by nelmarmoreno 8 years ago
what are the rules in rounding off numbers?
by Katherine Tyrrell 4 years ago
I'm wondering if the HubPages Team are aware that earnings data on Amazon sales exported to a csv file is much less useful than it could be because the $ notation is a prefix to each amount rather than topping the column.That means it's impossible to sum the data using a formula.In other words the...
by sid_candid 5 years ago
I am getting an error with Microsoft excel calculation. I am using Microsoft Excel 2003 and when calculating with a formula I am getting #VALUE!. The formula is put as =C2*.25*B2*.4*.05. Please help me solve this problem.
by Holly 9 years ago
My daughter is 21 months old. Just about every night (for the past 6 weeks) she throws up (periodically during the day too), sometimes she does it when having a tantrum, but often it occurs when she is sleeping (I'll hear her make a normal sound & then she vomits-or ill just hear her vomit) and...
by Gymfroggy 8 years ago
Greetings all,I didn't spot an introduction forum so posting here instead. I am a website developer from the UK and look forward to creating useful hubs for everyone to enjoy.Please take a look at my first proper hub http://hubpages.com/hub/Computer-Tricks … ed-To-Know and let me know what...
Copyright © 2019 HubPages Inc. and respective owners. Other product and company names shown may be trademarks of their respective owners. HubPages® is a registered Service Mark of HubPages, Inc. HubPages and Hubbers (authors) may earn revenue on this page based on affiliate relationships and advertisements with partners including Amazon, Google, and others.
|HubPages Device ID||This is used to identify particular browsers or devices when the access the service, and is used for security reasons.|
|Login||This is necessary to sign in to the HubPages Service.|
|HubPages Traffic Pixel||This 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.|
|Remarketing Pixels||We 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 Pixels||We 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.|