rounding to 5 cents 
Author Message
 rounding to 5 cents

Hello,

From the very old times (version 3 of Excel), I used the formula :

=SUM((A1:A3)*2);1)/2

for rounding to 5 cents.

Is there a more clever way to do the same with Execl 97 ?

Thanks




Wed, 18 Jun 1902 08:00:00 GMT  
 rounding to 5 cents
I don't think your system works any longer (in versions 5.0 and above).
If you are trying to round the sum of A1:A3 to the nearest nickel, I
would use:

=ROUND(SUM(A1:A3),1)

Karen

Quote:

> Hello,

> From the very old times (version 3 of Excel), I used the formula :

> =SUM((A1:A3)*2);1)/2

> for rounding to 5 cents.

> Is there a more clever way to do the same with Execl 97 ?

> Thanks





Wed, 18 Jun 1902 08:00:00 GMT  
 rounding to 5 cents


Quote:
>=SUM((A1:A3)*2);1)/2

>for rounding to 5 cents.

>Is there a more clever way to do the same with Execl 97

There's the MROUND function in the Analysis Tool Pak.

        =MROUND(SUM(A1:A3),0.05)

Or you can write a more general formula (which doesn't require the ATP) to
round to the nearest multiple of some number N, like this:

  =ROUND(A1/N,0)*N

For rounding to the nearest 5 cents, the formula would be

  =ROUND(SUM(A1:A3)/0.05,0)*0.05



Wed, 18 Jun 1902 08:00:00 GMT  
 rounding to 5 cents
On Tue, 22 Sep 1998 16:38:06 -0400, Karen Feigenbaum

Quote:

>If you are trying to round the sum of A1:A3 to the nearest nickel, I
>would use:

>=ROUND(SUM(A1:A3),1)

For me, that rounds to the nearest dime.


Wed, 18 Jun 1902 08:00:00 GMT  
 rounding to 5 cents
Just have to add some math... (add 0.02 to your number and then take the
FLOOR, or conversely subtract 2 cents from your number and take the CEILING)

Given 2.34

Function = FLOOR(number,significance) = FLOOR (2.36,.05)
Result = 2.35

Given 2.31
Result = 2.30

Given 2.29
Result = 2.30

etc...

Dave

Quote:

>Try the CEILING or the FLOOR functions.

>Given 2.34

>Function =CEILING(number, significance)  =CEILING(2.34, .05)

>Equals 2.35

>The only downside of this is that Ceiling always rounds up to the nearest
>significance...Floor rounds down.

>So that given "2.31" you'd get 2.35 as an answer with the Ceiling function.


>>On Tue, 22 Sep 1998 16:38:06 -0400, Karen Feigenbaum

>>>If you are trying to round the sum of A1:A3 to the nearest nickel, I
>>>would use:

>>>=ROUND(SUM(A1:A3),1)

>>For me, that rounds to the nearest dime.



Wed, 18 Jun 1902 08:00:00 GMT  
 rounding to 5 cents
Try the CEILING or the FLOOR functions.

Given 2.34

Function =CEILING(number, significance)  =CEILING(2.34, .05)

Equals 2.35

The only downside of this is that Ceiling always rounds up to the nearest
significance...Floor rounds down.

So that given "2.31" you'd get 2.35 as an answer with the Ceiling function.

Quote:

>On Tue, 22 Sep 1998 16:38:06 -0400, Karen Feigenbaum

>>If you are trying to round the sum of A1:A3 to the nearest nickel, I
>>would use:

>>=ROUND(SUM(A1:A3),1)

>For me, that rounds to the nearest dime.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Excel - the ROUND function to round in 5 cent multiples

2. Rounding dollars and cents

3. Rounding to 5 cents

4. Rounding to 5 cents

5. Rounding dollars and cents

6. Rounding Currency to the nearest 5 cents - HELP!

7. Rounding a number to the nearest 5 cent

8. Rounding To nearest 05 Cents in Excel 97

9. Round up to nearest 5 cents?

10. Round a number up to 99 cents


 
Powered by phpBB® Forum Software © phpBB Group