Is there a way to roundup numbers such that the integer is ignored and only the decimal is rounded up? I know how to round up numbers to nearest nickel, etc., and I know how to round up by multiples. My problem is I need the cents to round up to one of three numbers that are not multiples – .29, .59, or .99.
For example, if my prices are as follows:
6.15, 6.47, 6.89
I need the numbers to roundup to the following:
6.29, 6.59, 6.99
If all of my prices were between 6.00 and 6.99, I can make columns to make that happen, then use the MIN function so the applicable number populates the price column. The problem is that my prices vary from under a dollar to over $300.00, so I would need to create multiple columns for every integer and let the MIN function take lowest figure.
So I’m wondering instead of having columns for items costing 5.00-5.99, columns for 6.00-6.99, etc., if there is a way to make a formula that ignores the integer, so it ignores the 5 in 5.33 and the 6 in 6.33, looks at them as X.33 and rounds them up to 5.59 and 6.59.
If your cell is, for example, A1, the integer part is given by FLOOR(A1), and the decimal part of the price is therefore A1 - FLOOR(A1). You could then use a set of nested IF statements to change the decimal part to the desired amount.
I haven’t fired up Excel and tried it, but I think this should work:
(This equation assumes that there aren’t any prices whose decimal parts are between 0.99 and 0.9999… If none of the input prices involve fractional cents, this shouldn’t be a problem.)
Note also, the solution by MikeS assumes that you only have positive numbers to be thus adjusted. If your data includes negative numbers as well (probably not, given OP’s problem statement), then you must first clarify exactly what the rule should be for negative numbers, and then add more clauses to that gorilla IF expression to handle them.
Nitpick: INT is the function that gives the integer part of positive numbers. FLOOR requires a second argument: the multiple to which you want to round the number down to. FLOOR.MATH gives you the best of both worlds (a multiple argument with a default value of 1), but that’s requires Office 2013 (or 2011 for Mac).
Another option you might like is using a lookup table and the VLOOKUP function with the range_lookup parameter set to true. You can set up a table on a new sheet or wherever’s convenient to you that looks like this:
0 0.29
0.3 0.59
0.6 0.99
and then using a formula like (assuming the value you’re rounding up is A1 and the table is named “roundup”):
=INT(A1)+VLOOKUP(A1-INT(A1),roundup,2,TRUE)
This may be better if you expect the “target” values to change in value or number. (You’ll have to keep the lookup table in numerical order if you make changes to it.) This may be worse if you have a ton of values to convert, since VLOOKUP is so so slow.
I’d also be of the mind that vlookup is the cleanest way to do this, but an alternative approach is below:
ColA =Price
ColB =INT(ColA)
ColC =Mod(ColA,1)
ColD =IF(ColC=0,0,IF(ColC<0.3,0.29,IF(ColC<0.6,0.59,0.99)))
ColE =ColB+ColD
Thanksm, all. I first tried the FLOOR suggestion, but a box popped up saying I had too few arguments. Then tried INT instead of FLOOR and all looks ok. Thanks for the help.
Those are arbitrary numbers the boss decided he wanted - all prices to end in .29, .59, or .99. It is because on certain product lines, our cost actually varies a little, but we want to sell everything in the group at the same price. In one family of products, one color might be several cents higher than most of the others. So in one group, the formulas for most of the colors might come up 6.21, a few might be 6.15, and one might be 6.26. We have all prices for the group the same, so Group X are all 6.29.
If you have any expectation the boss will want more price or different buckets eventually, then you definitely want to use the VLOOKUP approach rather than the ginourmous IF(…) approach.
That way one small change in the lookup table will reliably propagate to all the computations.