Rounding off dollar amounts in formula

[gary]

I have a large price list that my client wants me to round off prices
to the nearest $5.00 amounts.

The problem is I am increasing prices form an existing list (adding
%). This yields new prices with single dollar and cents amounts. I
want the new price to be rounded to the closest $5.

Does anyone know how to accomplish this.

Thanks!

GP

 

[JE McGimpsey]

I have a large price list that my client wants me to round off prices
to the nearest $5.00 amounts.

The problem is I am increasing prices form an existing list (adding
%). This yields new prices with single dollar and cents amounts. I
want the new price to be rounded to the closest $5.

One way:

B1: =ROUND(A1*(1+xx%)/5,0)*5

so, for instance to add 5% and round to the nearest $5

=ROUND(A1*(1+5%)/5,0)*5

 

[Barry Wainwright [MVP]]

One way:

B1: =ROUND(A1*(1+xx%)/5,0)*5

so, for instance to add 5% and round to the nearest $5

=ROUND(A1*(1+5%)/5,0)*5

Use 'Ceiling' or 'Floor' to round up or down, or use 'mround' (needs the
analysis toolpack to be installed) to round to nearest.

A1: 11.32
B1: =ceiling(a1+5%,5) -> 15
C1: =floor(A1+5%,5) -> 10
D1: =mround(a1+5%,5) -> 10

 

[JE McGimpsey]

Use 'Ceiling' or 'Floor' to round up or down, or use 'mround' (needs the
analysis toolpack to be installed) to round to nearest.

A1: 11.32
B1: =ceiling(a1+5%,5) -> 15
C1: =floor(A1+5%,5) -> 10
D1: =mround(a1+5%,5) -> 10

Note that

=CEILING(A1+5%,5)

adds 0.05 to A1 then rounds up. It does NOT add 5% of A1. To increase A1
by 5%, use

=CEILING(A1 + A1*5%, 5)

or the equivalent, and more efficient:

=CEILING(A1 * (1+5%),5)

or the even more efficient

=CEILING(A1*1.05,5)

I tend to avoid using MROUND at all, since it may not be available to
someone I send a workbook to. A rather large number of my corporate
clients don't have the ATP installed, and don't have access to it.

Using the built-in functions instead of MROUND:

=ROUND(A1*1.05/5,0)*5

[or =ROUND(A1*0.21,0)*5) ]

works for all versions of XL.


NOTE: I believe that all the ATP functions were brought into XL2007 from
the ATP, so one can hope that MacXL2008 will also, in which case
MROUND() may come to be preferred.

 

[Barry Wainwright [MVP]]

adds 0.05 to A1 then rounds up. It does NOT add 5% of A1. To increase A1
by 5%, use

Sorry, my typo - that's what happens when you type it out instead of trying
it in Excel!