I would like to round for example 430 to return 400 and 431 to return 500. Not sure that is possible??
I would like to round for example 430 to return 400 and 431 to return 500. Not sure that is possible??
First off, welcome to the forum.
Not sure what MS office version Iowa is, please update your MS version as the answers given can depend on the version you have.
Also, you might be able to do that but it would be important to know what the parameters are that would indicate which direction that rounding should go, for example 431 is much closer to 400 than it is to 500. To develop a formula it would be good to know when to go up vs down.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Try it this way:
=ROUND(A1+19,-2)
assuming your number is in A1.
Hope this helps.
Pete
I would like to be able to change the break point for rounding up to the even 100 point or down to the even 100 point. For example <=541 =500 and >541 =600
Ok that worked perfectly for the example given!! What is "19" in this formula? I thought it would be the break point variable but it does not work that way??
if you use =ROUND(A1,-2) it will take anything below 450 and round it down to 400 and anything from 450 up to 549 and round it to 500. 19 is the distance below 450 that 431 is so adding it to 431 makes it 450 so it rounds 431 and anything up to 531 to 500 and anything below 431 to 400.
hope that helps.
With standard rounding, anything above (and including) 50 will round up to the next 100, and anything below 50 will round down. You want to round up from 31 and upwards, which is a difference of 19 from the standard round point, hence the +19 to adjust the rounding point. Any of the numbers from 431 to 530 inclusive will round to 500 (try it, with consecutive numbers in column A and the formula in B).
Hope this helps.
Pete
Thanks guys this was exactly what I was hoping for!!
That's good to hear, Steve.
If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.
Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks