Conditional Rounding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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)
 
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)
 
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?
 
=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)
 
Thank you so much!

Bob Phillips said:
=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)
Click to expand...
 
How do I include $x.89 value in the below formula to add $0.05 with the $x.09
& $x.59 value?

Thanks!
 
Back
Top