I am working on a spreadsheet and trying to get a formula to do the following. Say A1 is $365 and I want B1 to round to the nearest 9, so $369 then add 40, so B1 will be $409. If A1 was $363 it would be $399. I need help with the formula because I cannot quite get it to get the answer I need it to. Thanks.
MacBook Pro (Retina, Mid 2012), OS X Yosemite (10.10)
What if A1 is $364? Would B1 be $359+$40, or $369+40?
OK, this is not really rounding in the accepted sense of smoothing to a specific number of decimal places.
This is testing the last digit of a number and finding out the number you need to add or subtract to get to 9 (based on arbitrary rules of when to go up and when to go down), and then doing a sum with the result.
Here's a table, with sample figures in row 2:
In the first column (B) is the original price.
In the second column (C), is the final digit of the price - as text. (Calculating it separately makes it easier to write the formula in the next column.) The formula here is
RIGHT(B2,1)
In the third column (D) is the complicated bit:
IF (C2=”0”,”-1”,(IF(C2=”1”,”-2”,(IF(C2=”2”,”-3”,(IF(C2=”3”,”-4”,(IF(C2=”4”,”-5”,(I F(C2=”5”,”4”,(IF(C2=”6”,”3”,(IF(C2=”7”,”2”,(IF(C2=”8”,”1”,”0”)))))))))))))))))
e.g. if C2 is 0, then we need to subtract 1, or if C2 is 1, then we need to subtract 2... or if C2 is 5, then we need to add 4 etc etc.) If the final digit is not in the range 0-8 then it must be 9, so the "rounding" is 0.
I chose to subtract 5 from 4 rather than add it because I'm being generous to my customers 😝 But it's arbitrary.
The single figures in the formula are all in quotes because at this point we are dealing with text comparisons rather than numbers.
In the fourth column (E) is the formula
=B2+D2+40
This is the original figure, plus the positive or negative "rounding", plus 40. (The text in D2 is silently coerced to a number.)
Changing the value of the original price changes the marked-up price as well:
There's probably a much easier way...
Something along these lines should work:
=(ROUND(A1 + 1, -1) + 39
This will get it always to round up to the 9 in the ones place.
=CEILING(E2×0.1,1)×10 + 39
E2 is the location of the number.
Another possibility is =ROUND(A1,−1)+39
It gives a slightly different result than the ROUND formula provided by Jeff or the CEILING formula provided by Charles. With A1=364, the answer will be 399 instead of 409. Same idea with other numbers that end with 4. Until the question asked by HD is answered, in one knows which way these values should round.
ROUND and MAX functions in Numbers
