Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# formula excel

Posted on 2014-10-01
Medium Priority
324 Views
I need to create a formula for dynamic rounding. Here are the pricing rules

.00 to .09 rounds down to .99
.10 to .18 rounds up to .19
.20 to .28 rounds up to .29
.30 to .38 rounds up to .39
.40 to .48 rounds up to .49
.50 to .58 rounds up to .59
.60 to .68 rounds up to .69
.70 to .78 rounds up to .79
.80 to .88 rounds up to .89
.90 to .98 rounds up to .99

Exception  .19,.29,.39,.49,.59,.69,.79,.89,.99 no change
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 24

Expert Comment

ID: 40355493
=int(a1)+choose(mod(int(a1)*10,10)+1,-1,1,2,3,4,5,6,7,8,9)/10+0.09
0

LVL 27

Accepted Solution

Glenn Ray earned 2000 total points
ID: 40363085
Phillip, with your formula I only get values that are 0.01 less than the original whole number value (ex. 2.49 --> 1.99; 14.32 --> 13.99).

ccampbell107, I presume that you want the decimal value of any positive number (a price) to be fully rounded and not just the decimal value only.  For example, if the original value is 10.07, you want 9.99 as the result; if the original value was 10.45, then you want 10.49.  I note that you haven't specified what to do if the price is between 0.01 and 0.09.  For the purpose of this exercise, any values in that range will remain unaltered.

The formula below should meet your requirements.  It presumes the original values are in column A starting in cell A1.  Add this formula in row 1 of an adjacent column and copy down as needed:
=IF(A1<0.1,A1,IF(ROUNDDOWN(A1-INT(A1),1)*10=0,INT(A1)-0.01,ROUNDDOWN(A1,1)+0.09))

See the example workbook.

Regards,
-Glenn
EE-Q-28529296.xlsx
0

Author Comment

ID: 40363705
thank you very much. It is precisely what I needed.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
###### Suggested Courses
Course of the Month6 days, 20 hours left to enroll