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

March 30, 2009 11:15 AM Subscribe

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?

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?

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, 2009

posted by preparat at 11:28 AM on March 30, 2009

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, 2009

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, 2009

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, 2009

Thanks all for your input.

posted by emjay at 11:44 AM on March 30, 2009

*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, 2009 [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, 2009

Whether 1.999 is better than 2.009 is up to the poster tho.

posted by mrt at 12:12 PM on March 30, 2009

« Older Polarized Caravan for less than an arm and/or leg | How to get a public policy research job in DC? Newer »

This thread is closed to new comments.

posted by mrt at 11:26 AM on March 30, 2009