| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
| Rounding | Excel Worksheet Functions | |||
| conditional rounding | Excel Worksheet Functions | |||
| Conditional Rounding | Excel Worksheet Functions | |||
| Rounding | Excel Worksheet Functions | |||
Linear Mode
