Solved

formula excel

Posted on 2014-10-01
3
294 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 500 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

To stay competitive, modern businesses must adapt and stay innovative, and this is increasingly only possible by working with outside talent. Managers and executives have understood the power of outsourcing for quite some time, but traditional clien…
With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

785 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