You spin me round right baby right round, like a record...

March 30, 2009 11:15 AM

Need to get Excel to ROUND properly in a custom way, details inside...

I have a spreadsheet that pulls gas price data from a database (which comes from to me in the form of an .xls spreadsheet)

Sample Data shows as an average of several prices:

A 2.096
B 2.085
C 2.137
D 1.991
E 2.013
F 2.034

I need to round these numbers in Excel to:

A 2.099
B 2.089
C 2.139
D 1.999
E 2.019
F 2.039


So always rounding UP to the 9-Thousandths.

I've tried ROUND, ROUNDUP, ROUNDDOWN, CEILING, MAX, MOD and various combinations.

So far, the only way I've found to do it has been:
=ROUNDDOWN(2.034,2)+.009

I'm concerned that the ROUNDDOWN function may not always work properly as ROUNDUP sometimes has anomolies for Thousandths <> 5

Is there an easier formula to use tha doesn't require adding back in the .009?

posted by emjay to computers & internet (8 comments total)

=ROUNDUP(A1,2)-0.001
posted by mrt at 11:26 AM on March 30

I tried doing =LEFT(A1,4)&9 with the first value being in A1. This is a cheap way of getting what you're after. I say cheap because it will not work for gas prices greater than 9.99.
posted by preparat at 11:28 AM on March 30

mrt FTW.
posted by preparat at 11:29 AM on March 30

I originally as going to say what mrt said, but if these are gas prices, it looks to me like you're really chopping off the last digit and replacing it with a 9 (because otherwise 1.991 should become 1.989 or something). I don't know how to do that off the top of my head, but maybe someone else can get you an exact formula for my idea if it helps.

on preview: I think that's what preparat is doing, but I can't test his formula right now to check
posted by niles at 11:32 AM on March 30

After testing, mrt seems to have gotten it right and more reliably than my ROUNDDOWN.

Thanks all for your input.
posted by emjay at 11:44 AM on March 30

I tried doing =LEFT(A1,4)&9 with the first value being in A1. This is a cheap way of getting what you're after. I say cheap because it will not work for gas prices greater than 9.99.

=MID(A1, 1, LEN(A1)-1) & "9"

It means:
Take the first x digits from the price, where x is one less than the full length of the price. (So, all but the last digit.) Then append a nine.

This avoids rounding altogether, and works for gas>$10.
posted by inigo2 at 12:05 PM on March 30 [1 favorite]

^ 2.000 should be a test case for this, too.

Whether 1.999 is better than 2.009 is up to the poster tho.
posted by mrt at 12:12 PM on March 30

If the number provided is 2.000, 2.009 is preferable.
posted by emjay at 10:59 AM on March 31