a function on decimals-last two digits

Try

=ROUND((A1*100000)/25,0)*25/100000


"chartasap" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -->
take
> last two digits '38' compared to .00025. If it's less than .00025 then
those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

Because it is rounding to the nearest .00025, not as per your actual criteria.

HTH,
Bernie
MS Excel MVP


> It works great however, when i used the function below for values such as
> 1.52866, 1.52821, 1.52775, 1.52730 etc. the result is not correct. Any idea
> why it won't work on these?

Thank you Duke...this is definitely another way i can go.

"Duke Carey" wrote:

> One more alternative
>
> =TRUNC(A1,3)+0.0005*(MID(A1,SEARCH(".",A1)+4,2)>25)
>
> "chartasap" wrote:
>
> > Hello,
> >
> > would someone be able to help me build a function that would take the last
> > two digits in a value compare it then return a '0' or '5'. For example,
> >
> > Column A
> > 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> > last two digits '38' compared to .00025. If it's less than .00025 then those
> > two last digits should become a 0 if greater than .00025 then it should be
> > '5'.
> > RESULT
> > 1.5315
> > ---------
> > 1.53047 --> 1.5305
> > 1.53002 --> 1.5300
> > 1.52957 --> 1.5295
> >
> > Thank you!
> >

Thank you Bernie...i tried this formula and it works wonderfully. Thank you
for your time.

"Bernie Deitrick" wrote:

> For a value in cell A1:
>
> =IF(A1*1000-INT(A1*1000)>=0.25,INT(A1*1000)/1000+0.0005,INT(A1*1000)/1000)
>
> You didn't say what to do it it were equal to 25, so I chose >=, but you can change that.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "chartasap" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > would someone be able to help me build a function that would take the last
> > two digits in a value compare it then return a '0' or '5'. For example,
> >
> > Column A
> > 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> > last two digits '38' compared to .00025. If it's less than .00025 then those
> > two last digits should become a 0 if greater than .00025 then it should be
> > '5'.
> > RESULT
> > 1.5315
> > ---------
> > 1.53047 --> 1.5305
> > 1.53002 --> 1.5300
> > 1.52957 --> 1.5295
> >
> > Thank you!
> >
>
>
>

Barb,

It works great however, when i used the function below for values such as
1.52866, 1.52821, 1.52775, 1.52730 etc. the result is not correct. Any idea
why it won't work on these?

"Barb Reinhardt" wrote:

> Try
>
> =ROUND((A1*100000)/25,0)*25/100000
>
>
> "chartasap" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > would someone be able to help me build a function that would take the last
> > two digits in a value compare it then return a '0' or '5'. For example,
> >
> > Column A
> > 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -->
> take
> > last two digits '38' compared to .00025. If it's less than .00025 then
> those
> > two last digits should become a 0 if greater than .00025 then it should be
> > '5'.
> > RESULT
> > 1.5315
> > ---------
> > 1.53047 --> 1.5305
> > 1.53002 --> 1.5300
> > 1.52957 --> 1.5295
> >
> > Thank you!
> >
>
>
>

Oops - make that

=TRUNC(A1,3)+0.0005*(VALUE(MID(A1,SEARCH(".",A1)+4,2))>25)

"chartasap" wrote:

> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> last two digits '38' compared to .00025. If it's less than .00025 then those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

One more alternative

=TRUNC(A1,3)+0.0005*(MID(A1,SEARCH(".",A1)+4,2)>25)

"chartasap" wrote:

> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> last two digits '38' compared to .00025. If it's less than .00025 then those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

For a value in cell A1:

=IF(A1*1000-INT(A1*1000)>=0.25,INT(A1*1000)/1000+0.0005,INT(A1*1000)/1000)

You didn't say what to do it it were equal to 25, so I chose >=, but you can change that.

HTH,
Bernie
MS Excel MVP


"chartasap" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> last two digits '38' compared to .00025. If it's less than .00025 then those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

One more alternative

=TRUNC(A1,3)+0.0005*(MID(A1,SEARCH(".",A1)+4,2)>25)

"chartasap" wrote:

> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> last two digits '38' compared to .00025. If it's less than .00025 then those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

Oops - make that

=TRUNC(A1,3)+0.0005*(VALUE(MID(A1,SEARCH(".",A1)+4,2))>25)

"chartasap" wrote:

> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> last two digits '38' compared to .00025. If it's less than .00025 then those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

Barb,

It works great however, when i used the function below for values such as
1.52866, 1.52821, 1.52775, 1.52730 etc. the result is not correct. Any idea
why it won't work on these?

"Barb Reinhardt" wrote:

> Try
>
> =ROUND((A1*100000)/25,0)*25/100000
>
>
> "chartasap" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > would someone be able to help me build a function that would take the last
> > two digits in a value compare it then return a '0' or '5'. For example,
> >
> > Column A
> > 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -->
> take
> > last two digits '38' compared to .00025. If it's less than .00025 then
> those
> > two last digits should become a 0 if greater than .00025 then it should be
> > '5'.
> > RESULT
> > 1.5315
> > ---------
> > 1.53047 --> 1.5305
> > 1.53002 --> 1.5300
> > 1.52957 --> 1.5295
> >
> > Thank you!
> >
>
>
>

For a value in cell A1:

=IF(A1*1000-INT(A1*1000)>=0.25,INT(A1*1000)/1000+0.0005,INT(A1*1000)/1000)

You didn't say what to do it it were equal to 25, so I chose >=, but you can change that.

HTH,
Bernie
MS Excel MVP


"chartasap" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> last two digits '38' compared to .00025. If it's less than .00025 then those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

Thank you Bernie...i tried this formula and it works wonderfully. Thank you
for your time.

"Bernie Deitrick" wrote:

> For a value in cell A1:
>
> =IF(A1*1000-INT(A1*1000)>=0.25,INT(A1*1000)/1000+0.0005,INT(A1*1000)/1000)
>
> You didn't say what to do it it were equal to 25, so I chose >=, but you can change that.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "chartasap" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> >
> > would someone be able to help me build a function that would take the last
> > two digits in a value compare it then return a '0' or '5'. For example,
> >
> > Column A
> > 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> > last two digits '38' compared to .00025. If it's less than .00025 then those
> > two last digits should become a 0 if greater than .00025 then it should be
> > '5'.
> > RESULT
> > 1.5315
> > ---------
> > 1.53047 --> 1.5305
> > 1.53002 --> 1.5300
> > 1.52957 --> 1.5295
> >
> > Thank you!
> >
>
>
>

Thank you Duke...this is definitely another way i can go.

"Duke Carey" wrote:

> One more alternative
>
> =TRUNC(A1,3)+0.0005*(MID(A1,SEARCH(".",A1)+4,2)>25)
>
> "chartasap" wrote:
>
> > Hello,
> >
> > would someone be able to help me build a function that would take the last
> > two digits in a value compare it then return a '0' or '5'. For example,
> >
> > Column A
> > 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
> > last two digits '38' compared to .00025. If it's less than .00025 then those
> > two last digits should become a 0 if greater than .00025 then it should be
> > '5'.
> > RESULT
> > 1.5315
> > ---------
> > 1.53047 --> 1.5305
> > 1.53002 --> 1.5300
> > 1.52957 --> 1.5295
> >
> > Thank you!
> >

Try

=ROUND((A1*100000)/25,0)*25/100000


"chartasap" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> would someone be able to help me build a function that would take the last
> two digits in a value compare it then return a '0' or '5'. For example,
>
> Column A
> 1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) -->
take
> last two digits '38' compared to .00025. If it's less than .00025 then
those
> two last digits should become a 0 if greater than .00025 then it should be
> '5'.
> RESULT
> 1.5315
> ---------
> 1.53047 --> 1.5305
> 1.53002 --> 1.5300
> 1.52957 --> 1.5295
>
> Thank you!
>

Because it is rounding to the nearest .00025, not as per your actual criteria.

HTH,
Bernie
MS Excel MVP


> It works great however, when i used the function below for values such as
> 1.52866, 1.52821, 1.52775, 1.52730 etc. the result is not correct. Any idea
> why it won't work on these?

Hello,

would someone be able to help me build a function that would take the last
two digits in a value compare it then return a '0' or '5'. For example,

Column A
1.53138 (this value is the result of a formula =sum(c6/5873.05-c3) --> take
last two digits '38' compared to .00025. If it's less than .00025 then those
two last digits should become a 0 if greater than .00025 then it should be
'5'.
RESULT
1.5315
---------
1.53047 --> 1.5305
1.53002 --> 1.5300
1.52957 --> 1.5295

Thank you!