If you round to the nearest 5 or 9, then 7.01 to 11.99 rounds to 9 and 12.01 to 16.99 rounds to 15. *This site is protected by reCAPTCHA and the Google You also can use this formula =MROUND(A2,5) to round numbers to nearest 5. Experts Exchange is the only place where you can interact directly with leading experts in the technology field.

Our community of experts have been thoroughly vetted for their expertise and industry experience. Syntax: FLOOR (number, significance) Number - the number you want to round. NB_VC I've already put your formula to use it met my needs at the time however after checking hyundt's formula it does do the correct rounding. Become a member today and access the collective knowledge of thousands of technology experts.

Before you take it to the moderator, what is the desired answer if cell B2 contains 602.45?

If you round to the nearest 5 or 9, then 7.01 to 11.99 rounds to 9 and 12.01 to 16.99 rounds to 15. I don't believe the suggested formula gives the desired answer in all cases. I will get with a moderator and split the points if that is OK with you both?

If you use MROUND (B2,5) to round to the nearest 5, it will incorrectly round to xx0 from 12 to 12.49. If byundt is correct, and my formula does not produce expected results (I did not test all possibilities, I tested a few and the results seemed ok) and his does, then by all means have the points removed by a moderator and then re-assign appropriately. If you need to round a number to the nearest multiple of 5, you can use the MROUND function and supply 5 for number of digits. Round to next nearest 5/10/50/100 A different (and shorter) approach, though, is to use the ROUNDDOWN function to do the rounding, in this manner: =ROUNDDOWN(A1,1)+0.09 You could also use the straight ROUND function in this manner: =ROUND(A1+0.05,1)-0.01 Shorter still is a solution that doesn't rely on any of the built-in rounding functions: =(INT(A1*10)+0.9)/10 This award recognizes someone who has achieved high tech and professional accomplishments as an expert in a specific topic. For example, with B2=602.45, the formula returns 599 when it ought to return 605.We help IT Professionals succeed at work. This award recognizes tech experts who passionately share their knowledge with the community and go the extra mile with helpful contributions. 1 =CEILING(B3,5) Of course, you can use.5 to round up to the nearest.5 instead. Costs of 7 or 12 could could be rounded either way (they are equally distant from xx9 and yy5). How this formula works In the example, cell C6 contains this formula: = MROUND(B6,5) To round up to the nearest 5, use the CEILING Function with multiple = 5.

Significance - the multiple to which you wish to round the number. 8+ characters (letters, numbers, and a symbol)https://www.experts-exchange.com/questions/28218147/Round-to-the-nearest-5-or-9-dollar.html I knew it was simple just could not get it, brain is fried, bu thank you for coming through in a pinch!

(Get your first solution completely free - no credit card required) UNLOCK SOLUTION

=1*(ROUNDDOWN(A2/10,0)&INDEX({0,5,9},MATCH(MIN(ABS({0,5,9}-MOD(A2,10))),ABS({0,5,9}-MOD(A2,10)),0))) MOD to get right digit of cell; Find the distance between {0,5,9} and the right digit and MATCH it to the closest digit; Concatenate right of the matched digit to the left digit and multiply by 1 Experts with Gold status have received one of our highest-level Expert Awards, which recognize experts for their valuable contributions.

The FLOOR function in Excel is used to round a given number down, to the nearest multiple of a specified significance. Costs of 7 or 12 could could be rounded either way (they are equally distant from xx9 and yy5). Our community of experts have been thoroughly vetted for their expertise and industry experience.

The two formulas return different results using that input.