R1C1 Formula and FIND function

[dilshod_k]

Hello everyone.

Given:

Columns P and Q

Column P contains random values (Number Format) interspaced with unknown number of empty cells

The aim is to write code which returns value into “Q” Cell and fulfils criteria as it follows below:

If P Cell value = Nothing then Q Cell value = Nothing

If P Cell value > 0 then Q Cell value = (Next P Cell value (down the column) if it is > 0 – Current P Cell value)/Current P Cell value



For Example:

“P2” = “””” “Q2” = “”””

“P3” = 50 “Q3” = (150 – 50)/50

“P4” = “””” “Q4” = “”””

“P5” = “””” “Q5” = “”””

“P6” = 150 “Q6” = (80 – 150)/80

“P7” = “””” “Q7” = “”””

“P8” = 80 “Q8” = “””” if the rest of the values of Column P = “”””



I tried to use Formula:

FormulaR1C1 = " = IF (AND ( RC [-1] = “””” ), """" , IF ( AND ( RC [-1] > 0 ),""Text message"",""""))"

But I did not know how to incorporate FIND function instead of “Text message”

Would be grateful for any suggestions.

Thanks in advance.

Dil

 

[jasonb75]

Try posting a sample of proper data with simple formulas to show the results by using XL2BB (link below) and asking your question in plain english instead of psuedo code.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[dilshod_k]

Analyser.xlsm
	P	Q
1	SMA Values	SMA Crossover
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20	92.76
21
22
23
24
25
26
27
28
29
30	97.62
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65	140.60
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92	136.90
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115	137.75
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150	129.71
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166	129.44
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183	128.90
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234	134.15
235
236
237	135.73
238
239
240
241
242
243
244
245
246
247
248
BXP

Cell Formulas
Range	Formula
P2:P248	P2	=IF(AND(I2>J2,I3<J3),I2,IF(AND(I2<J2,I3>J3),I2, ""))

 

[jasonb75]

That's not a sample, that's a column of formulas. There is nothing to show how the FIND function that you want to add to the formula relates to the data.

A sample should include 20-30 rows of data, not 200. It should show the results that you want and an explanation of how you get those results from the data shown.

 

[dilshod_k]

Hello again,

Sorry for confusing explanation.
I could not figure out what kind of function or formula to use in Column Q.
To explain as an example I'll use ranges Q20, Q30, Q92.
The value in Q cells should be:
Q20 = (P30-P20)/P20
Q30 = (P65 - P30)/P30
Q92 = (P92 - P65)/P65 e.t.c

Q cells with adjacent empty P cells should remain empty.

Obviously that the last value in Column P will not have one of the numerators and will give an error.

Thanks again.
Dil

 

[dilshod_k]

That's not a sample, that's a column of formulas. There is nothing to show how the FIND function that you want to add to the formula relates to the data.

A sample should include 20-30 rows of data, not 200. It should show the results that you want and an explanation of how you get those results from the data shown.

Sorry, I did not know how to submit explanation along with table, that is why I'm sending them separately.
Thanks,
Dil.

 

[jasonb75]

So to be sure that I'm following correctly, you need a formula that is effectively =(x-y)/y

Where y refers to the number in the same row as the formula and x refers to the next number found looking down the column?

To add an explanation with the table, you can type it before or after the table code, just be sure not to change the code itself. Most people find it easier to type the explanation first, tap enter twice to create an empty line, then paste the table code below the empty line.

 

[dilshod_k]

Not exactly.
If the formula is z = (x-y)/y
Z is the resulting value and it should be in column Q
Whereas both X and Y values are in the column P
The value which is Y in one calculation will become X in the next one.
For instance: P30 is an analogue of X in the formula Q20
and P30 is an analogue of Y in the formula Q30.
So the value which was X in one calculation becomes Y in the next one.

 

[jasonb75]

I think that's the same as what I said, just phrased differently. Try applying this to a range in column Q and see what happens.

I've assumed that there will always be less then 100 empty rows between numbers in column P, you can increase this if needed by increasing the number inside the square brackets R[100]. this needs to be done in 2 places, you can't use full columns and making the number too high will likely cause problems, the other square brackets R[1] should not be changed.

FormulaArray = "=IFERROR(IF(RC16="""","""",(INDEX(R[1]C16:R[100]C16,MATCH(TRUE,ISNUMBER(R[1]C16:R[100]C16),0))-RC16)/RC16),"""")"

 

[dilshod_k]

It is working fine! Thanks a lot! Appreciate it. ?