Charmed Price Problem

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.

1. Price rounded down to 10 cents will give $2.30
2. MOD(2.37,0.1) gives 0.07
3. This is falls in to the else portion of 2nd IF formula
   1. IF(MOD(2.37,0.1)<=0.02,-0.01, IF(MOD(2.37,0.1)<=0.05,0.05,0.09))
4. So we add 9 cents to the rounded down price.
5. 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