?
Solved

formula excel

Posted on 2014-10-01
3
Medium Priority
?
344 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

578 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