An Interesting Excel Question

Today I received an interesting Excel question from a user and thought I’d share the solution I came up with here.

Here was the question: In excel, what is the formula for taking a cost times a 30% margin then rounding it up to the next $x.x5 or $x.x9     (example: $3.45 x 30% margin = $4.93, roundup to $4.95) or (example: $3.48 x 30% margin = $4.97, roundup to $4.99)

At first, I thought, there’s no easy way to do this. And there isn’t. A quick search for functions that would do this specific task resulted in nothing.

But then I remembered that I’d covered the CHOOSE function in Excel 2019 Formulas & Functions and that it could be used for this. As it turns out, it still wasn’t easy to do, but it is doable.

(And there may very well be a much simpler solution to the problem, but this seemed to work for me, so it’s what I went with.)

Here’s what I ultimately came up with:

=ROUND(A1/0.7,2)+IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,CHOOSE(RIGHT(ROUND(A1/0.7,2)),0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)))

The first part of that formula, =ROUND(A1/0.7,2) is calculating the marked-up price on a value in Cell A1. That gives the $4.93 or the $4.97 value.

The second part of that formula is where it gets interesting. Let’s drop out the IF functions for now and look at the CHOOSE function:

CHOOSE(RIGHT(ROUND(A1/0.7,2)),0.04,0.03,0.02,0.01,0,0.03,0.02,0.01,0)))

It’s best to work from the inside out.

So we start with the (ROUND(A1/0.7,2) portion which is just giving us the number we’re working with.

Next is the RIGHT function. What that’s doing is taking the right-most digit of our number. So in the examples above that would either be the 3 of $4.93 or the 7 of $4.97.

Finally, we have the CHOOSE function which basically provides a different result depending on the value you give it. So in this case a 1 returns a value of .04. A 2 returns a value of .03, etc. Once we cross the 5 mark we have to switch things up a bit so that 6, 7, 8, and 9 will result in values that end in a 9 instead of values that end in a 5.

That value gets added to our original calculated value to create a result that ends in either a 5 or a 9.

But because I used the RIGHT function we have a problem. Any value that’s a whole number, like $9.00, isn’t going to calculate properly because RIGHT will pull in a value of 9 not 0. And any value like $7.30, $8.20, etc. will also not work properly because RIGHT will pull a 3 or a 2 or whatever the next actual digit is instead of the zero.

We can solve that, though, with IF functions. Namely,

IF(TRUNC(A1/0.7)-A1/0.7=0,0.05,

and

IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05,

IF(TRUNC(A1/0.7)-A1/0.7=0,0.05 basically says that if it’s a whole number then add .05 to it. And IF(ROUND(A1/0.7,1)-ROUND(A1/0.7,2)=0,0.05 says that if the rounded value with one digit is the same as the rounded value with two digits, then also add .05.

A little messy, but it solves those two issues.

And I think all of it taken together answers solves the issue.