Formula problem: works sporadically

newmember · May 13th, 2002, 02:10 PM

I have a sheet with about 60K rows and columns upto AM. I have a formula:
"=100 * PERCENTRANK(V:V,AO4,2)"

This worked when I entered it but it stopped working after some time. The exact same formula worked sporadically, and now is not working again. What's happening?

Thanks!

Aladin Akyurek · May 13th, 2002, 02:21 PM

Quote:

On 2002-05-13 13:10, newmember wrote:
I have a sheet with about 60K rows and columns upto AM. I have a formula:
"=100 * PERCENTRANK(V:V,AO4,2)"

This worked when I entered it but it stopped working after some time. The exact same formula worked sporadically, and now is not working again. What's happening?

Thanks!

What result do you get when it doesn't work?

newmember · May 13th, 2002, 02:27 PM

No result: the cell contains the text for the formula:
"=100 * PERCENTRANK(V:V,AO4,2)"

(without the quotation marks, but including the = (equals) sign). i.e the cell shows the formula instead of the result. it is as if the sheet is not evaluating it. But I have the evaluation set to automatic and F9 does not work either. Also, the complexity of the formula does not matter; when the above formula is not working "=1+1" also does not work.

Thanks!

Aladin Akyurek · May 13th, 2002, 02:48 PM

Quote:

On 2002-05-13 13:27, newmember wrote:
No result: the cell contains the text for the formula:
"=100 * PERCENTRANK(V:V,AO4,2)"

(without the quotation marks, but including the = (equals) sign). i.e the cell shows the formula instead of the result. it is as if the sheet is not evaluating it. But I have the evaluation set to automatic and F9 does not work either. Also, the complexity of the formula does not matter; when the above formula is not working "=1+1" also does not work.

Thanks!

I believe the underlying format of the worksheet is changed to text. Make an additonal copy of the target sheet within the workbook. See how the new copy behaves. If that's OK, you might want to delete the original sheet.

Asala42 · May 13th, 2002, 02:48 PM

Hey I've seen that,

Usually that happens when those cells were formatted as text somewhere along the line. Even switching the format to number or general does not immediately solve the problem.

This usually solves the problem (which I've learned from this board btw). Select the effected cells, then hit data/text to columns/finish. Should do the trick.

Hope that works,
Adam

Mark W. · May 13th, 2002, 03:00 PM

Reformat the cells containing the text representation of your formula, "=100 * PERCENTRANK(V:V,AO4,2)", using the General format. With these cells still selected choose the Edit | Replace... menu command and replace = with =.

newmember · May 13th, 2002, 03:06 PM

Thanks a tonne guys. You were both correct.

Complete fix was copying into a new sheet.

Thanks!

newmember · May 13th, 2002, 03:12 PM

mark: that was perfect. Thanks.

I repeat your solution for reference later:

---------------------------------------
Reformat the cells containing the text representation of your formula, "=100 * PERCENTRANK(V:V,AO4,2)", using the General format. With these cells still selected choose the Edit | Replace... menu command and replace = with =.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 02:10 PM	#1
newmember New Member Join Date: May 2002 Posts: 8	I have a sheet with about 60K rows and columns upto AM. I have a formula: "=100 * PERCENTRANK(V:V,AO4,2)" This worked when I entered it but it stopped working after some time. The exact same formula worked sporadically, and now is not working again. What's happening? Thanks!

May 13th, 2002, 02:27 PM	#3
newmember New Member Join Date: May 2002 Posts: 8	No result: the cell contains the text for the formula: "=100 * PERCENTRANK(V:V,AO4,2)" (without the quotation marks, but including the = (equals) sign). i.e the cell shows the formula instead of the result. it is as if the sheet is not evaluating it. But I have the evaluation set to automatic and F9 does not work either. Also, the complexity of the formula does not matter; when the above formula is not working "=1+1" also does not work. Thanks!

May 13th, 2002, 02:48 PM	#5
Asala42 Board Regular Join Date: Feb 2002 Location: Southfield,MI USA Posts: 1,030	Hey I've seen that, Usually that happens when those cells were formatted as text somewhere along the line. Even switching the format to number or general does not immediately solve the problem. This usually solves the problem (which I've learned from this board btw). Select the effected cells, then hit data/text to columns/finish. Should do the trick. Hope that works, Adam

May 13th, 2002, 03:00 PM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Reformat the cells containing the text representation of your formula, "=100 * PERCENTRANK(V:V,AO4,2)", using the General format. With these cells still selected choose the Edit \| Replace... menu command and replace = with =.

May 13th, 2002, 03:06 PM	#7
newmember New Member Join Date: May 2002 Posts: 8	Thanks a tonne guys. You were both correct. Complete fix was copying into a new sheet. Thanks!

May 13th, 2002, 03:12 PM	#8
newmember New Member Join Date: May 2002 Posts: 8	mark: that was perfect. Thanks. I repeat your solution for reference later: --------------------------------------- Reformat the cells containing the text representation of your formula, "=100 * PERCENTRANK(V:V,AO4,2)", using the General format. With these cells still selected choose the Edit \| Replace... menu command and replace = with =.