sumit Posted May 29, 2009 Share Posted May 29, 2009 i have the following numbers 2 4 23 57 11 25 3 here each number is different i want to replace numbers with multiples of 5 so that the output collumn would not have all the numbers different e.g replace 2 with 0 replace 4 with 5 replace 23 with 25 replace 57 with 60 replace 11 with 10 25 remains 25 replace 3 with 5 so new collumn is 0 5 25 60 10 25 5 so here 5 is repeated twice and 25 is repeated twice therefore we have acheived less variations in the collumn Now my problem is how to acheive this with input collumn is an excel file and output collumn have to be created in a new excel file. the data is going to be huge. Please suggest me how i can write this code . I am attaching input excel file if required. Thanksinput.zip Link to comment Share on other sites More sharing options...
Zedna Posted May 29, 2009 Share Posted May 29, 2009 Look at Mod() and Excel UDF in helpfile. Resources UDF ResourcesEx UDF AutoIt Forum Search Link to comment Share on other sites More sharing options...
MrMitchell Posted May 29, 2009 Share Posted May 29, 2009 There may be an easier way to do it, I'm not yet familiar with EVERYTHING AutoIt can do, but you can take the given number, divide by 5, then the remainder/modulus can be used to determine if you should round up or down. $number = 66 $mod = Mod( $number, 5 ) ;$mod = 1 If $mod = 1 Then $number -= 1 If $mod = 4 Then $number += 1 ... Might want to read the entire set of numbers into an array first (_ExcelReadArray), open a new book, process the array and either write to the new cells as you go (_ExcelWriteCell) or write to another array first then push it to the new cells (_ExcelWriteArray). I guess which way you go depends what else you plan on doing with all this information. My [slow] computer did the whole set in like 45 seconds using this approach, seems kind of slow though. Link to comment Share on other sites More sharing options...
Malkey Posted May 30, 2009 Share Posted May 30, 2009 There may be an easier way to do it, I'm not yet familiar with EVERYTHING AutoIt can do, but you can take the given number, divide by 5, then the remainder/modulus can be used to determine if you should round up or down. $number = 66 $mod = Mod( $number, 5 ) ;$mod = 1 If $mod = 1 Then $number -= 1 If $mod = 4 Then $number += 1 ... ....This line round up or down to the nearest 5. $RndUnm = $Num - (Mod($Num, 5) <= 2) * Mod($Num, 5) + (Mod($Num, 5) > 2) * (5 - Mod($Num, 5)) An example ; Local $res, $RndUnm For $Num = 5 To 23 #cs $mod = Mod( $Num, 5 ) If $mod <= 2 Then $RndUnm = $Num - $mod If $mod > 2 Then $RndUnm = $Num + (5 - Mod($Num, 5)) #ce $RndUnm = $Num - (Mod($Num, 5) <= 2) * Mod($Num, 5) + (Mod($Num, 5) > 2) * (5 - Mod($Num, 5)) $res &= $Num & " is rounded to " & $RndUnm & @CRLF Next MsgBox(0, "Rounded Result", $res) ; Link to comment Share on other sites More sharing options...
sumit Posted May 30, 2009 Author Share Posted May 30, 2009 I REALLY APPRECIATE THE HELP :-) THANKS Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now