Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

formula excel

Posted on 2014-10-01
3
Medium Priority
?
340 Views
Last Modified: 2014-10-06
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
Comment
3 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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

by:
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

by:Connie Campbell-Pearson
ID: 40363705
thank you very much. It is precisely what I needed.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This video walks the viewer through the process of creating an MLA formatted document, as well as a bibliography with citations.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question