Need normal formula instead volatile

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
Hello,</SPAN>

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">3 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">3</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=MATCH(<font color="Blue">1000,INDEX(<font color="Red">FIND(<font color="Green">" | "&ROW(<font color="Purple">INDIRECT(<font color="Teal">"1:999"</font>)</font>)&" | "," | "&E3&" | "</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,433
Office Version
2019
Platform
Windows
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.
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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>
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,276
Office Version
365
Platform
Windows
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))
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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>
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,433
Office Version
2019
Platform
Windows
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.
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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>

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">3 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 1</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">3</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G3</th><td style="text-align:left">=MATCH(<font color="Blue">1000,INDEX(<font color="Red">FIND(<font color="Green">ROW(<font color="Purple">$G$1:$G$9</font>),E3</font>),0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Kind Regards,
</SPAN>
Moti
</SPAN></SPAN>
 
Last edited:

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,276
Office Version
365
Platform
Windows
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))
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,782
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>

I appreciate your help
</SPAN></SPAN>

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


 
Last edited:

Forum statistics

Threads
1,085,581
Messages
5,384,574
Members
401,909
Latest member
dlo1503

Some videos you may like

This Week's Hot Topics

Top