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

 Page 1 of 1 [ 6 post ]

Relevant Pages