Counting Cell or Range

[muhammad susanto]

hi all..

how to counting this :

Excel 2007 32 bit

	E​
3​	data
4​	3​
5​
6​	after formula
7​	=E4+1

Sheet: Sheet1

i want the result is =E5 (E4+1) not 4 included mark "="

any help is greatly appreciated..

m.susanto

 

[muhammad susanto]

more clear...

Excel 2007 32 bit

	E​
3​	data
4​	3​
5​
6​	after formula
7​	=E5
8​
9​	note : E4+1=E5
10​	(included mark "=")

Sheet: Sheet1

 

[muhammad susanto]

how to calculate this below :
=E4+1=E5......final answer included mark "=" so =E5

how do it?

 

[Scott Huish]

You can't put =E4+1 to turn it into =E5.
You can offset from E4 if you want and it will return the value from E5 but it won't actually say =E5

=OFFSET(E4,1,0)

 

[muhammad susanto]

hello Scott, thanks a lot...

 

[muhammad susanto]

You can't put =E4+1 to turn it into =E5.
You can offset from E4 if you want and it will return the value from E5 but it won't actually say =E5

=OFFSET(E4,1,0)

hi scott...how to solve this :

Excel 2007
	E
3	data
4	a
5	b
6	c
7	d
8	etc..
9
10	after formula
11	a
12
13
14	b
15
16
17	c
Sheet1

 

[Scott Huish]

Assuming text data as in your example

In E11:
=T(IF(MOD(ROWS($A$1:A1),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A1)/3)+3),""))

Copy down.

Book1
	E
3	data
4	a
5	b
6	c
7	d
8
9
10
11	a
12
13
14	b
15
16
17	c
18
19
20	d
21
22
23
24
Sheet1

Cell Formulas
Range	Formula
E11	=T(IF(MOD(ROWS($A$1:A1),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A1)/3)+3),""))
E12	=T(IF(MOD(ROWS($A$1:A2),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A2)/3)+3),""))
E13	=T(IF(MOD(ROWS($A$1:A3),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A3)/3)+3),""))
E14	=T(IF(MOD(ROWS($A$1:A4),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A4)/3)+3),""))
E15	=T(IF(MOD(ROWS($A$1:A5),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A5)/3)+3),""))
E16	=T(IF(MOD(ROWS($A$1:A6),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A6)/3)+3),""))
E17	=T(IF(MOD(ROWS($A$1:A7),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A7)/3)+3),""))
E18	=T(IF(MOD(ROWS($A$1:A8),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A8)/3)+3),""))
E19	=T(IF(MOD(ROWS($A$1:A9),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A9)/3)+3),""))
E20	=T(IF(MOD(ROWS($A$1:A10),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A10)/3)+3),""))
E21	=T(IF(MOD(ROWS($A$1:A11),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A11)/3)+3),""))
E22	=T(IF(MOD(ROWS($A$1:A12),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A12)/3)+3),""))
E23	=T(IF(MOD(ROWS($A$1:A13),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A13)/3)+3),""))
E24	=T(IF(MOD(ROWS($A$1:A14),3)=1,INDEX(E:E,-INT(-ROWS($A$1:A14)/3)+3),""))

 

[muhammad susanto]

HI SCOTT...can you explained where came from number 3?

 

[Scott Huish]

You're putting data in every 3rd row.

 

[muhammad susanto]

HI SCOTT..
Please help me for this problem :
Excel 2007 32 bit

	L​	M​	N​
111​			DATA
112​			5020499999-08
113​			5020405999-03
114​			5020405999-01
115​			5020403002-03
116​			5020403002-02
117​			5020403002-01
118​			5020401999-42
119​			5020401999-36
120​			5020401999-23
121​			5020401999-22
122​			5020401999-21
123​			5020401999-19
124​			5020401999-17
125​			5020401999-09
126​			5020401999-07
127​			5020401999-05
128​			5020401999-02
129​			5020303003-11
130​			5020303002-06
131​			5020401999-09
132​			5020401999-07
133​			5020401999-05
134​			5020401999-02
135​			5020303003-11
136​			5020303002-06
137​			5020401999-23
138​			5020401999-22
139​			5020401999-21
140​			5020401999-19
141​			5020401999-22
142​
143​
144​
145​
146​
147​
148​
149​
150​
151​
152​
153​
154​
155​
156​
157​
158​
159​
160​
161​
162​
163​
164​
165​
166​
167​
168​	EXPECTED RESULT
169​	5020499999-08
170​
171​
172​
173​
174​
175​
176​
177​
178​
179​
180​
181​
182​
183​
184​
185​	5020405999-03
186​
187​
188​
189​
190​
191​
192​
193​
194​
195​
196​
197​
198​
199​
200​
201​	5020405999-01

Sheet: Sheet1

interval row = 16
my target in cell L169, L185, L201