How can I round prices in Excel?

December 20, 2007 3:08 PM

In Excel 2007, I need advice on how to round a series of prices to either a $0.50 ending, or a $0.95 ending.

The first digit or two (the dollars) of the price will vary. The rounding should only occur in the last two digits (the cents).

Prices from $X.00 to $X.49, round up to $X.50.
Prices from $X.51 to $X.94, round up to $X.95.
Prices from $X.96 to $X.99, round down to $X.95.

I was thinking an If Then statement, but not sure how to deal with the differing dollar part of the price.

I'm thinking this is probably easy, and I am just missing something.

Any advice will be greatly appreciated.

posted by Futurehouse to computers & internet (10 comments total)

I'm not very familiar with Excel's scripting abilities, but in your if-then, can you declare anything before the decimal point as a wildcard? Like so:

"if (value <>= *.00)
then (whatever)"

(yes, that was C++. Kinda)

I don't know if there's a way to do that, but it seems like you'd be able to say " *.50" to mean "anything ending in .50".
posted by DMan at 3:22 PM on December 20, 2007

Looking back, it messed with my code since I didn't use the proper tags. Ignore my C++ example, my last sentence is the real answer.
posted by DMan at 3:22 PM on December 20, 2007

Presuming your price is in A1:

=IF(A1-INT(A1)<0.5,INT(A1)+0.5,INT(A1)+0.95)
posted by pompomtom at 3:24 PM on December 20, 2007

Try this formula:
=IF(A1-TRUNC(A1,-2)<51,TRUNC(A1,-2)+50,TRUNC(A1,-2)+95)
(replace A1 with whatever cell contains the relevant data.)

The key ingredient here is the TRUNCate function, which cuts off everything past a certain digit. TRUNC(A1,-2) cuts off starting with two spaces to the left of the decimal point.

If dollars and cents are in decimal form, rather than whole-number form, replace with TRUNC(A1,0) or just TRUNC(A1), and replace 51, 50, and 95 with .51, .50, and .95.
posted by CrunchyFrog at 3:25 PM on December 20, 2007

pompomtom has got the form right, but you actually want

=IF(A1-INT(A1)<>
or else $5.50 will be rounded to $5.95
posted by ssg at 3:28 PM on December 20, 2007

Oops, of course my version should be

=IF(A1-INT(A1)<0>1,INT(A1)+0.5,INT(A1)+0.95)
posted by pompomtom at 3:30 PM on December 20, 2007

and, when not being eaten by mefi:



=IF(A1-INT(A1)<0.51,INT(A1)+0.5,INT(A1)+0.95)
posted by pompomtom at 3:31 PM on December 20, 2007

ugh, i forgot to format my bracket properly:

=IF(A1-INT(A1)<=0.5,INT(A1)+0.5,INT(A1)+0.95)
posted by ssg at 3:31 PM on December 20, 2007 [1 favorite has favorites]

Instead of the (A1-INT(A1)), you should be able to just do RIGHT(A1,3).
posted by inigo2 at 7:34 PM on December 20, 2007

RIGHT(A1,3) will work fine as long as all your prices are correctly formatted with two decimal places. If they are just left in Excel's default format, you will get the wrong result from values like $25.10, which will be stored as 25.1.
posted by ssg at 8:40 AM on December 21, 2007