working with decimal remainder

[Guest]

hi all,

if i have a number in a cell (say 2.456) and i want to work with ONLY the
0.456 - is there a function that will let me do that

i can write a function in vba to take care of everything - i would prefery
to stay with worksheet functions as much as possible.

basically i am working with dollars and cents - and i want to create a
conditional function that will adjust the prices according to
$X.00 -> $(X-1).99
$X.01 - $X.24 -> $X.25
$X.25 - $X.49 -> $X.50
$X.50 - $X.99 -> $X.99

thanks for your help!

J

 

[Tom Ogilvy]

=Mod(a1,1)

works for me.

 

[Guest]

thanks Tom

I feel kind of stupid now.... =)

=Mod(a1,1)

works for me.

 

[Guest]

Hi,
Building on Tom's reply:

=CHOOSE(MATCH((MOD(A1,1)),{0.999,0.4999,0.2499},-1),0.99,0.5,0.25)

will give you the "rounded" figures. To avoid possible errors with the
number of decimal places, it might worth will making the cents an integer and
then comparing against integer comparators.

HTH

 

[Guest]

Seeing as this is what i ended up with:

=IF(MOD((H7*(1+N7)),1)=0,(H7*(1+N7))-0.01,IF(AND(MOD((H7*(1+N7)),1)>0.01,MOD(H7*(1+N7),1)<=0.25),H7*(1+N7)-MOD(H7*(1+N7),1)+0.25,IF(AND(MOD(H7*(1+N7),1)>0.25,MOD(H7*(1+N7),1)<=0.5),H7*(1+N7)-MOD(H7*(1+N7),1)+0.5,IF(AND(MOD(H7*(1+N7),1)>0.5,MOD(H7*(1+N7),1)<=0.75),H7*(1+N7)-MOD(H7*(1+N7),1)+0.75,IF(AND(MOD(H7*(1+N7),1)>0.75,MOD(H7*(1+N7),1)<=0.99),H7*(1+N7)-MOD(H7*(1+N7),1)+0.99)))))

i think yours is a tad simpler =)

J

 

[Guest]

Using IF:

=IF(MOD(A1,1)<0.25,INT(A1)+0.25,IF(MOD(A1,1)<0.5,INT(A1)+0.5,INT(A1)+0.99))

 

[Dana DeLouis]

if i have a number in a cell (say 2.456) >

basically I am working with dollars and cents

Hi. If I am not mistaken, the other excellent ideas didn't round a number
like 5.00 down to 4.99.
Here is just one of a few ideas. If you have in A1 a number like 2.456,
then in B1, I would Round this to 2 decimal places with:
=ROUND(A1,2)

Then perhaps in C1, a formula like.
=CEILING(B1+0.01,0.25)+0.24*(MOD(B1,1)>=0.5)-0.25*(MOD(B1,1)>=0.75)-0.26*(MOD(B1,1)=0)

A helper cell to hold MOD(A1,1) might be helpful too.
HTH :>)

 

[Guest]

Further to Dana's note:

=IF(MOD(A1,1)=0,(A1-1)+0.99,IF(MOD(A1,1)<0.25,INT(A1)+0.25,IF(MOD(A1,1)<0.5,INT(A1)+0.5,INT(A1)+0.99)))


HTH

 

[Dana DeLouis]

Hi. Another idea along your technique might be to factor out the Int(A1)
part. Maybe something like this:

=INT(A1)+IF(MOD(A1,1)=0,-0.01,IF(MOD(A1,1)<0.25,0.25,IF(MOD(A1,1)<0.5,0.5,0.99)))