If Function and rounding numbers

[DM]

Ok, can someone help me out? I am trying to create formula that is
going to round my numbers to x.47 or x.97.
A1=$9.99
B1=75%
C1=A1-B1=$2.49
So what I am trying to do is to take discount from price and if it
ends x.49 I would like to round it x.47 and if it ends x.99 I would
like to round it to x.97.

Does this make sense….so everything between x.51 and x.99 should be
rounded to x.97 and everything between x.01 to x.49 should be rounded
to x.47.

 

[RidgeView]

Use the following function where you want the rounded number to appear. C5
in this case is the number to be rounded.

=IF(ROUND(C5,0)<=C5,CONCATENATE(ROUND(C5,0),".47"),CONCATENATE(ROUNDDOWN(C5,0),".97"))

 

[joeu2004]

A1=$9.99
B1=75%
C1=A1-B1=$2.49

First, C1 should be =A1*(1-B1)

Second, that does not result in 2.49 exactly. If the C1 has the General
format, you will see 2.4975. I assume that is what you mean, because if the
cell had a numeric format with 2 decimal places, you would see 2.50, not 2.49.

everything between x.51 and x.99 should be
rounded to x.97 and everything between x.01
to x.49 should be rounded to x.47.

I presume you mean to also say: x.00nn and x.50nn should result in x.00 and
x.50 respectively.

Does the following meet your needs?

=--(int(A3) &
lookup(mod(int(100*A3),100),{0,1,50,51},{".00",".47",".50",".97"}))

Note: The formula does not work when A3 is 134217728 or larger, due to a
defect in the MOD algorithm (Excel 2003). But I doubt that will be a problem
since that's over $134 million.


----- original posting -----

 

[joeu2004]

Minor errata....

=--(int(A3) &
lookup(mod(int(100*A3),100),{0,1,50,51},{".00",".47",".50",".97"}))

Note: The formula does not work when A3 is 134217728 or larger,
due to a defect in the MOD algorithm (Excel 2003).

Actually, it does not work when A3 is greater than 134217727.999999. Even
though that is the largest constant less than 134217728 that we can enter,
calculations can have results between those two values. In particular, the
formula does not work when the calculation result in A3 is 134217727.999999 +
34*2^-26 or larger. But such values display as 134217728 anyway.


----- original posting -----