Sign in to follow this  
Followers 0
sumit

using excel for this problem

5 posts in this topic

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.

Thanks

input.zip

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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)
;

Share this post


Link to post
Share on other sites

I REALLY APPRECIATE THE HELP :-) THANKS

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0