Adding the same action to every second line

Solved/Closed
DG83 Posts 38 Registration date Monday 2 January 2012 Status Member Last seen 21 April 2018 - 13 Jan 2013 à 16:26
 Dg83 - 15 Jan 2013 à 01:11
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!
Related:

3 responses

rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
13 Jan 2013 à 16:34
you can use formula too
Which please?
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 Jan 2013 à 07:49
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)
Thanks! Its absolutely clear now!
DG83 Posts 38 Registration date Monday 2 January 2012 Status Member Last seen 21 April 2018
14 Jan 2013 à 12:52
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 Jan 2013 à 13:27
Sorry I missed the "5" part

You can do this

=A1 + (5* (MOD(ROW(),2)))
DG83 Posts 38 Registration date Monday 2 January 2012 Status Member Last seen 21 April 2018
14 Jan 2013 à 15:12
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!
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
14 Jan 2013 à 15:17
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