Need normal formula instead volatile

motilulla

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

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,618
Office Version
  1. 365
Platform
  1. 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,829
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,493
Office Version
  1. 365
Platform
  1. 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,829

ADVERTISEMENT

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
12,618
Office Version
  1. 365
Platform
  1. 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,829

ADVERTISEMENT

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,493
Office Version
  1. 365
Platform
  1. 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,829
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,521
Messages
5,636,812
Members
416,942
Latest member
kitkat22

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top