Rounding to the nearest 5

**Redder Lurtz** · 01-27-2011, 05:16 AM

Hi,

I have a long list of prices which I need to round to the nearest £5. What's the best way of doing this?

Many thanks

RL

Sample attached

**zbor** · 01-27-2011, 05:17 AM

1,96 should round to 0 or 5 and 6,84 round to 5 or 10?

**Domski** · 01-27-2011, 05:18 AM

=ROUND(A2/5,0)*5

or to round up:

=ROUNDUP(A2/5,0)*5

Dom

**Redder Lurtz** · 01-27-2011, 06:14 AM

Originally Posted by Domski

=ROUND(A2/5,0)*5

or to round up:

=ROUNDUP(A2/5,0)*5

Dom

Thanks Dom.

Is there also a way of expressing the figures as betewwn 0 and 5, 5 and 10, 10 and 15 etc?

Cheers

RL

**Domski** · 01-27-2011, 06:26 AM

Originally Posted by Redder Lurtz

Is there also a way of expressing the figures as betewwn 0 and 5, 5 and 10, 10 and 15 etc?

Maybe:

=IF(FLOOR(A2,5)=A2,FLOOR(A2,5)-5,FLOOR(A2,5))&" to "&CEILING(A2,5)

Dom

**arthurbr** · 01-27-2011, 06:19 AM

If you need to always round up to the nearest 5 maybe

=ceiling(a2,5)

will do

**Redder Lurtz** · 01-27-2011, 06:45 AM

Thanks Dom & others. Very useful as always.

RL

Re: Rounding to the nearest 5