Adding the same action to every second line

Solved/Closed
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018 - Jan 13, 2013 at 04:26 PM
 Dg83 - Jan 15, 2013 at 01:11 AM
hi guys,

i wonder if you could help me with the following:

there is a worksheet where column A is full with numbers. I would like to add to every second row 5. So if there is 10 in cell A1 I would like to see 15 there, if A3 15 the desired result is 20. Is there a way to create a macro that would be able to perform this string of action lets say until cell A50?

thanks a lot!

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 13, 2013 at 04:34 PM
you can use formula too
0
Which please?
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 14, 2013 at 07:49 AM
if number you want to add is on odd row, then you can use
=A1 + MOD(ROW(),2)

if you want to add for those rows which are on even row then you can use
=A1 + MOD(ROW()+1,2)
0
Thanks! Its absolutely clear now!
0
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018
Jan 14, 2013 at 12:52 PM
thanks a lot but it does not seem to return the required result.
Lets take this simple example: A1 is 1, A2 is 2, A3 is 3. I would like to add 5 to the odd rows, so B1 should return 6 and B3 8. Since A2 is an even row it should remain 2.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 14, 2013 at 01:27 PM
Sorry I missed the "5" part

You can do this

=A1 + (5* (MOD(ROW(),2)))
0
DG83 Posts 38 Registration date Monday January 2, 2012 Status Member Last seen April 21, 2018
Jan 14, 2013 at 03:12 PM
thanks, it has worked well! I just wonder why there is a multiple sign after 5 when it adds this amount to the required cells. What is the logic behind it please?
Thank you once again, my issue is resolved!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 14, 2013 at 03:17 PM
Row() is a function that gives the row number on which the function is called

mod is a function that returns a remainder. It has two parameter. First the number that you want to diviide and 2nd the number by which you want to divide

So mod(4,2) is saying divide 4 by 2 and return me the remainder which is 0

mod (1,2) is saying that divide 1 by 2 and return me the remainder which is 1

so dividing any number by 2 tells me if a row is odd (remainder would be 1) or even (remainder would be 0)

Any thing multiplied by 1 remains same, and any thing mulitiplied by 0 becomes 0

so 5 * 1 would be 5
and 5 * 0 would be 0
that answer i am adding to A1 to get the answer

hope it clarifies for you
0