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

=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

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

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

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

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

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

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

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