# Need normal formula instead volatile

#### motilulla

##### Well-known Member
Hello,</SPAN>

At present using following volatile formula does it is possible to be converted in the normal formula</SPAN></SPAN>

Book1
EFG
1
2
33 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 13
Sheet1
Cell Formulas
RangeFormula
G3=MATCH(1000,INDEX(FIND(" | "&ROW(INDIRECT("1:999"))&" | "," | "&E3&" | "),0))

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

Last edited:

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Just remove the volatile function.

=MATCH(1000,INDEX(FIND(" | "&ROW(\$1:\$999)&" | "," | "&E3&" | "),0))

It will work as long as you're not in the habit of deleting rows.

Just remove the volatile function.

=MATCH(1000,INDEX(FIND(" | "&ROW(\$1:\$999)&" | "," | "&E3&" | "),0))

It will work as long as you're not in the habit of deleting rows.
jasonb75, it is running fine thank you very much for your help</SPAN></SPAN>

Kind Regards,
</SPAN>
Moti
</SPAN>

Hi, here's a version that remains robust against row insertion and deletion.

=MATCH(1000,INDEX(FIND(" | "&ROW(INDEX(G:G,1):INDEX(G:G,999))&" | "," | "&E3&" | "),0))

Hi, here's a version that remains robust against row insertion and deletion.

=MATCH(1000,INDEX(FIND(" | "&ROW(INDEX(G:G,1):INDEX(G:G,999))&" | "," | "&E3&" | "),0))
FormR, tried it is also running fine, I noticed the difference in your formula clicking on formula bar this highlights only the column G instead of all area, thank you so much for your help</SPAN></SPAN>

Kind Regards,
</SPAN>
Moti
</SPAN></SPAN>

If you're trying to make the formula more efficient then you could remove the array concatenation as well, the formula would still work correctly.

=MATCH(1000,INDEX(FIND(ROW(\$G\$1:\$G\$999),E3),0))

If your original data only ever contains single digit numbers then you don't need \$999 as the end row, \$9 will suffice. This is probably the biggest improvement that you could make.

The method suggested by FormR is only needed if you're in the habit of deleting rows, if you don't delete rows then the index union is not necessary.

If you're trying to make the formula more efficient then you could remove the array concatenation as well, the formula would still work correctly.

=MATCH(1000,INDEX(FIND(ROW(\$G\$1:\$G\$999),E3),0))

If your original data only ever contains single digit numbers then you don't need \$999 as the end row, \$9 will suffice. This is probably the biggest improvement that you could make.

The method suggested by FormR is only needed if you're in the habit of deleting rows, if you don't delete rows then the index union is not necessary.
jasonb75, really I do not delete any rows, tested your formula rectified \$9 it is working well. Thank you for shorting it.</SPAN></SPAN>

Book1
EFG
1
2
33 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 13
Sheet1
Cell Formulas
RangeFormula
G3=MATCH(1000,INDEX(FIND(ROW(\$G\$1:\$G\$9),E3),0))

Kind Regards,
</SPAN>
Moti
</SPAN></SPAN>

Last edited:
Hi, If it's just single digit numbers you can also hard code them and maintain robustness (which I personally consider to be important, even if you don't intend to insert or delete rows).

=MATCH(1000,INDEX(FIND({1,2,3,4,5,6,7,8,9},E3),0))

Hi, If it's just single digit numbers you can also hard code them and maintain robustness (which I personally consider to be important, even if you don't intend to insert or delete rows).

=MATCH(1000,INDEX(FIND({1,2,3,4,5,6,7,8,9},E3),0))
FormR, I also consider this one is the best solution of my problem!</SPAN></SPAN>

</SPAN></SPAN>

Kind Regards,
</SPAN>
Moti
</SPAN>

Last edited:

Replies
0
Views
463
Replies
8
Views
251
Replies
7
Views
268
Replies
0
Views
802
Replies
5
Views
131

1,203,554
Messages
6,056,068
Members
444,841
Latest member
SF_Marnie

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back