# using excel for this problem

## Recommended Posts

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 on other sites

Look at Mod() and Excel UDF in helpfile.

##### 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 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 on other sites

I REALLY APPRECIATE THE HELP :-) THANKS

## Create an account

Register a new account

×

• Wiki

• Back

• Git