working with decimal remainder
 

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

working with decimal remainder
 

=Mod(a1,1)

works for me.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" wrote in message
...
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

working with decimal remainder
 

thanks Tom

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


"Tom Ogilvy" wrote:

=Mod(a1,1)

works for me.

--
Regards,
Tom Ogilvy

"Gixxer_J_97" wrote in message
...
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

working with decimal remainder
 

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

"Gixxer_J_97" wrote:

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

working with decimal remainder
 

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,M OD(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


"Toppers" wrote:

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

"Gixxer_J_97" wrote:

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

working with decimal remainder
 

Using IF:

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


"Gixxer_J_97" wrote:

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,M OD(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


"Toppers" wrote:

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

"Gixxer_J_97" wrote:

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

working with decimal remainder
 

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 :)
--
Dana DeLouis
Win XP & Office 2003


"Gixxer_J_97" wrote in message
...
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

working with decimal remainder
 

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" wrote:

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 :)
--
Dana DeLouis
Win XP & Office 2003


"Gixxer_J_97" wrote in message
...
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

working with decimal remainder
 

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

--
Dana DeLouis
Win XP & Office 2003


"Toppers" wrote in message
...
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" wrote:

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 :)
--
Dana DeLouis
Win XP & Office 2003


"Gixxer_J_97" wrote in message
...
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