*Here is a charming little problem to kick start your day.*

Lets say you run a cute little bakery around the corner. Since you want your prices to look charming, you have a policy to round them down or up based on below rule.

If the price ends with 0, 1 or 2 cents, round it down to 9 cents.

If the price ends with 3, 4 or 5 cents, round it up to 5 cents.

If the price ends with 6, 7, 8 or 9 cents, round it up to 9 cents.

**For example,**

*So how do you round to nearest charmed price?* You could do it manually. But you would rather bake a few more of those Tiny Cup Cakes than waste time rounding the prices. So you want an automatic way to round prices. This is where Excel helps.

## Formula for rounding to charmed price

There are many ways to write a formula for this.

### The first and most obvious method is to use IF formula

Assuming regular price is in cell C4, The formula for charmed price would look like this:

=ROUNDDOWN(C4,1) + IF(MOD(C4,0.1)<=0.02,-0.01, IF(MOD(C4,0.1)<=0.05,0.05,0.09))

Go ahead and take a hard look at it.

The first that strikes us when you read it would be,

*‘Gee, Thats one long formula. I need a coffee.’*

**How it works:**

- First we round down the price (in C4) to 10 cents with ROUNDDOWN(C4,1)
- Then we add or subtract few cents to get the charmed price with IF formula.
- IF the cents are less than or equal to 0.02, we subtract 1 cent
- IF the cents are between 3 & 5, we add 5 cents.
- Else, we add 9 cents.

So for example, if the actual price is $2.37, the formula gives $2.39 thru below process.

- Price rounded down to 10 cents will give $2.30
- MOD(2.37,0.1) gives 0.07
- This is falls in to the else portion of 2nd IF formula
- IF(MOD(2.37,0.1)<=0.02,-0.01, IF(MOD(2.37,0.1)<=0.05,0.05,0.09))

- So we add 9 cents to the rounded down price.
- Hence the charmed price is $2.39

[Related: Introduction to IF formula]

### An improvement – CHOOSE formula

If the *IF formula *is too long & difficult to write, we can choose *CHOOSE formula*.

It goes like this:

=C4 + CHOOSE(MOD(INT(C4*100),10)+1,-0.01,-0.02,-0.03,0.02,0.01,0,0.03,0.02,0.01,0)

This formula takes the price in C4 & adds or subtracts necessary cents to it to get the charmed price.

Examining it with $2.37 gives,

=2.37 + CHOOSE(MOD(INT(C4*100),10)+1,-0.01,-0.02,-0.03,0.02,0.01,0,0.03,0.02,0.01,0)

=2.37 + CHOOSE(8, -0.01,-0.02,-0.03,0.02,0.01,0,0.03,0.02,0.01,0)=2.37 + 0.02

=2.39

[Related: Introduction to CHOOSE formula]

### VLOOKUP & A mapping table

We can simplify our CHOOSE formula with a mapping table.

Lets say, somewhere in the workbook, we have set up a mapping table like this:

Then, we can use VLOOKUP formula to calculate charmed price:

=C4+VLOOKUP(MOD(INT(C4*100),10), mapping.table, 2 ,FALSE)

This formula is similar to CHOOSE formula.

**How it works?**

*Assuming actual price is $2.37,*

=2.37 + VLOOKUP(MOD(INT(C4*100),10), mapping.table, 2 ,FALSE)

=2.37 + 0.02 = 2.39

[Related: Introduction to VLOOKUP formula]

### VLOOKUP & A smaller mapping table

Using a combination of rounded down price & approximate lookup feature of VLOOKUP, we can come up with a smaller formula.

This requires a new mapping table like this:

Our formula now looks like this:

=ROUNDDOWN(C4,1) + VLOOKUP(MOD(INT(C4*100),10),new.mapping,2)

**How it works?**

=ROUNDDOWN(2.37,1) + VLOOKUP(MOD(INT(2.37*100),10),new.mapping,2)

=2.3 + VLOOKUP(MOD(237,10),new.mapping,2)

=2.3 + VLOOKUP(7,new.mapping,2)

=2.3 + 0.09

=2.39

## Download Example Workbook

**Click here to download charmed price example workbook**. Examine it to understand various formulas discussed in this article.

### Challenge for you – write another formula for charmed prices

**Here is a challenge for you.** Assuming the price is in C4, can you come up with another way to calculate charmed price? Please share your formulas in the comments section.

*Go ahead and charm us.*

### Want to charm your boss? Learn these as well

Excel spreadsheets are like *transmogrification cloaks. *If you put on the right ones, you will instantly become incredibly charming. So learn how to weave powerful spreadsheets and charm everyone around you. Start with these:

- 18.2 tips to rounding numbers in Excel
- Calculating new prices after % hike
- IF formula challenge for you

## 14 Responses to “Charmed Price Problem”

The first IF statement isn't working for me where the second decimal place is 0 - for me it is turning 4.10 into 4.19

My alternative:

=(IF(AND(MOD(INT(C4*100),10)>=3,MOD(INT(C4*100),10)<=5),5,9)+TRUNC(C4*10)*10+IF(MOD(INT(C4*100),10)<=2,-10))/100

Hi,

Wouldnยดt be possible also to use a boolean logic formula. Built similar to the if but just adding the data. Probably it would be the most efficient one.

As a programmer, I think you are over-complicating the solution. It is a simple if statement and the rounddown. This formula works, it is fairly simple and other than Excel giving the wrong answer for mod(2.73,.1) = .2999999999998. It works:

=ROUNDDOWN(H1,1) + IF(AND(MOD(H1,0.1)-0.03>=0,MOD(H1,0.1)-0.03<=0.02),0.05,0.09)

@Jay.. thanks for the comments.

Excel has floating point error which can cause weird behavior when dealing with fractional arithmetic.

NOT an array formula:

=IF(OR({3,4,5}=MOD(C4*100,10)),ROUNDUP(C4,1)-0.05,ROUND(C4,1)-0.01)

Two more to ponder.

1st one:

=ROUND(C4,1)-0.01+CHOOSE(MOD(C4*100,10),,,6,6,-4,,,,)/100

2nd one:

{=ROUND(C4,1)-0.01+SUM(({3,4,5}=MOD(C4*100,10))*{6,6,-4}/100)}

A different approach: assuming in A1:B10 we have a helping table like this:

0 -0.01

1 -0.02

2 -0.03

3 0.02

4 0.01

5 0

6 0.03

7 0.02

8 0.01

9 0

and our prices start in cell D1 the shortest formula I found is:

=VLOOKUP(RIGHT(D1*100,1)*1,$A$1:$B$10,2,0)+D1

Cheers.

If the numbers have more decimals that arenยดt shown because of format in the list given by chandoo your method would not work ๐

I assume that given example is actually a price and those tend to not go further than 2 decimals (in $/cent example) - at least those I've seen so far ๐

If I knew these aren't actual prices (for example some calculated values) I would just simply round them - problem solved ๐

I'd like to buy 10,000 Micro sampler cakes at the actual price of $0.02 each, please. Coooooeee, looks like you owe me $100! Thanks!

Jaja. True ๐

IF(OR(RIGHT(A1;1)="3";RIGHT(A1;1)="4";RIGHT(A1;1)="5");MROUND(A1;0.05);ROUND(A1;1)-0.01)

Hi Guys,

I saw smart answers for this homework and hope to get to this level soon. Though i'm new here I give bellow a solution that is still long but works:

C4+IF(VALUE(RIGHT(100*C4,1))<=2,-(RIGHT(100*C4,1)+1)/100,IF(VALUE(RIGHT(100*C4,1))<=5,+(5-(RIGHT(100*C4,1)))/100,+(9-(RIGHT(100*C4,1)))/100))

Regards,