Filling the ALTERNATE rows with sequential number (1, 2,3 ..

[aabbasi]

Is there a way to tell Excel to number the rows from 1 to 100. The only problem is that I want Excel to put this information in alternate rows. For example: A1=1, A2=none A3=2 A4=none A5=3 A6=none

Thank you

 

[PaddyD]

Hi,

Put a 1 in A1. In A3 put the following:

=IF(MOD(ROW(),2),A1+1,"")

& copy down.

paddy

 

[aabbasi]

Thank you for your response. However, when I followed your instructions, I got the following. Also if possible please explain me the function of MOD. Thanks

A1:1
A2:
A3:
A4:2
A5:
A6: #VALUE!
A7:
A8:#VALUE!

 

[IML]

You could put
=IF(MOD(ROW(),2),(ROW()+1)/2,"")
in row 1 and copy it down
(or the opposite
=IF(MOD(ROW(),2)=0,ROW()/2,"")

Mod simply returns the remainder. For example in row 3, it would return a 1 since 2 goes into 3 once with a remainder of 1. This is just used to check for even or odd rows. Excel interprets 0 as false, and a positve number as true.

 

[PaddyD]

Hi - works for me! See the example:

Book9
	A	B	C	D
1	1
2
3	2
4
5	3
6
7	4
8
9	5
10
11	6
12
13	7
Sheet1

i.e.
A1 - enter a 1
A2 - leave blank
A3 - enter the formula & copy down..

=IF(MOD(ROW(),2),A1+1,"")

row() / 2
If the row number / 2 leaves a remainder, then the row number is odd.
mod(row(),2) returns the remainder of the row() / 2 calculation.
It's use in the if() can be 'expressed' roughly as follows:

if the row number divided by 2 gives us anything, add 1 to A1, otherwise nothing.

(This works because excel will treat 0 as false and other numbers as true, which can be exploited within the true / false checking of the if statement).

Paddy

 

[aabbasi]

IML and Paddy: Thank you very much for giving me the solution. Paddy special thanks to you for explaining MOD function.

Have a nice evening!

regards,
aabbasi