Conditional Rounding
 

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

Not very elegant ( I am sure someone will improve on this):

=IF(OR(ROUND(MOD(INT((10*A1)+1)/10-0.01,1),2)=0.09,ROUND(MOD(INT((10*A1)+1)/10-0.01,1),2)=0.59),INT((10*A1)+1)/10,INT((10*A1)+1)/10-0.01)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

another ....

=IF(ISNA(MATCH(ROUND(MOD(INT((10*$A$1)+1)/10-0.01,1),2),{0.09,0.59},0)),INT((10*$A$1)+1)/10-0.01,INT((10*$A$1)+1)/10)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

How about

=INT((10*A1)+1)/10-0.01+(MOD(INT(A1*10)-(INT(A1)*10),5)=0)*0.01

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Toppers" wrote in message
...
Not very elegant ( I am sure someone will improve on this):

=IF(OR(ROUND(MOD(INT((10*A1)+1)/10-0.01,1),2)=0.09,ROUND(MOD(INT((10*A1)+1)/10-0.01,1),2)=0.59),INT((10*A1)+1)/10,INT((10*A1)+1)/10-0.01)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I append
to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

Thanks to all for your help. It works great. One more question, how do I
append to the formula to add $0.05 to only those values that results in
$x.09, $x.59 rather than rounding to the nearest $x.10?

"Toppers" wrote:

another ....

=IF(ISNA(MATCH(ROUND(MOD(INT((10*$A$1)+1)/10-0.01,1),2),{0.09,0.59},0)),INT((10*$A$1)+1)/10-0.01,INT((10*$A$1)+1)/10)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

=INT((10*A1)+1)/10-0.01+(MOD(INT(A1*10)-(INT(A1)*10),5)=0)*0.05

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"yk" wrote in message
...
Thanks to all for your help. It works great. One more question, how do I
append to the formula to add $0.05 to only those values that results in
$x.09, $x.59 rather than rounding to the nearest $x.10?

"Toppers" wrote:

another ....

=IF(ISNA(MATCH(ROUND(MOD(INT((10*$A$1)+1)/10-0.01,1),2),{0.09,0.59},0)),INT((10*$A$1)+1)/10-0.01,INT((10*$A$1)+1)/10)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to
the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I
append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

Thank you so much!

"Bob Phillips" wrote:

=INT((10*A1)+1)/10-0.01+(MOD(INT(A1*10)-(INT(A1)*10),5)=0)*0.05

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"yk" wrote in message
...
Thanks to all for your help. It works great. One more question, how do I
append to the formula to add $0.05 to only those values that results in
$x.09, $x.59 rather than rounding to the nearest $x.10?

"Toppers" wrote:

another ....

=IF(ISNA(MATCH(ROUND(MOD(INT((10*$A$1)+1)/10-0.01,1),2),{0.09,0.59},0)),INT((10*$A$1)+1)/10-0.01,INT((10*$A$1)+1)/10)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to
the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I
append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.

Conditional Rounding
 

How do I include $x.89 value in the below formula to add $0.05 with the $x.09
& $x.59 value?

Thanks!

"Bob Phillips" wrote:

=INT((10*A1)+1)/10-0.01+(MOD(INT(A1*10)-(INT(A1)*10),5)=0)*0.05

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"yk" wrote in message
...
Thanks to all for your help. It works great. One more question, how do I
append to the formula to add $0.05 to only those values that results in
$x.09, $x.59 rather than rounding to the nearest $x.10?

"Toppers" wrote:

another ....

=IF(ISNA(MATCH(ROUND(MOD(INT((10*$A$1)+1)/10-0.01,1),2),{0.09,0.59},0)),INT((10*$A$1)+1)/10-0.01,INT((10*$A$1)+1)/10)

"yk" wrote:

If I currenlty have a formula =INT((10*A1)+1)/10-0.01 to round up to
the
nearest $x.x9. However, if the results are $x.09, $x.59, how do I
append to
the formula to round up only $x.09and $x.59 to the nearest $x.10?

Thanks.