fbpx
Search
Close this search box.

Charmed Price Problem

Share

Facebook
Twitter
LinkedIn

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,

Charmed Price Problem - Rounding prices using Excel formulas

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:

Mapping Table 1 - Charmed Price Problem

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:

Mapping Table 2 - Charmed Price Problem - Excel formulas

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:

 

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

14 Responses to “Charmed Price Problem”

  1. Robert says:

    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

  2. Robert says:

    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

  3. Gonzalo says:

    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.

  4. Jay says:

    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)

  5. Daniel Ferry says:

    NOT an array formula:

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

  6. Daniel Ferry says:

    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)}

  7. Crisu says:

    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.

    • Gonzalo says:

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

      • Crisu says:

        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 ๐Ÿ™‚

  8. Peter says:

    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!

  9. Mark says:

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

  10. Luis G says:

    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,

Leave a Reply