**Query Source**: Excel Macros Google Group

**Solution Type**: Functions and Formulas

**Query by**: DM

**Solution by**: Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

**Query / Problem:**

*Ok, can someone help me out? I am trying to create formula that is going to round my numbers to x.47 or x.97.*

A1=$9.99

B1=75%

C1=A1-B1=$2.49

So what I am trying to do is to take discount from price and if it ends x.49 I would like to round it x.47 and if it ends x.99 I would like to round it to x.97. Does this make sense….so everything between x.51 and x.99 should be rounded to x.97 and everything between x.01 to x.49 should be rounded to x.47.

A1=$9.99

B1=75%

C1=A1-B1=$2.49

So what I am trying to do is to take discount from price and if it ends x.49 I would like to round it x.47 and if it ends x.99 I would like to round it to x.97. Does this make sense….so everything between x.51 and x.99 should be rounded to x.97 and everything between x.01 to x.49 should be rounded to x.47.

**Solution:**

Assuming the contents as follows

Column A (Actual Price) - A1 = 9.99

Column B (Discount) - B1 = 75%

Column C (Sale Price) - C1 = '=A1*(1-B1)'

The Solution would be

Column D (Tag Price) - D1 = '=FLOOR(C2,1)+IF(CEILING(C2,1)-(C2)<0.5,0.97,0.47)'

**Example:**

## Comments