My Excel file is not calculating!

[Accesshelp]

Good morning all,

I have an Excel file with two worksheets: Report and Data.

The worksheet Data has data where the data are stored, and the worksheet
Report is retrieving the data from Data to prepare a report by entering an ID
number. The data are retrieved using the vlookup formula in the Report
worksheet. The worksheet Data has 130 columns and 14,000 rows (up to) of
data.

The way it works is I enter an ID number in the worksheet Report. Then I
press F9 to do the calculation and the worksheet Report will start to
populate the data. Due to the size of Data worksheet, it takes about 2
minutes to have the Report worksheet fully populated.

The problem that I am having is the file freezes after I press F9. On the
bottom left corner, it shows Calculating Cells: 0% of Data worksheet, and it
would not move forward. I also tried to reduce the number of rows (from
14,000 to 300) to test whether it would help, and it did not help.

I have been using the file for months, and I have never had that problem
until recently. Recently, I made additions and deletions of columns in Data
and rows in Report, but the number of rows and columns stay the same from
before.

Does anyone have any idea of what causes, and how to resolve it?

Thanks.

 

[Niek Otten]

Try rebuilding the dependency tree with CTRL+ALT+SHIFT+F9

 

[Accesshelp]

Niek,

Thanks for your helps. As you instructed, I tried CTRL+ALT+SHIFT+F9 and
waited for 7 minutes. It's still not working. I also tried CTRL+ALT+F9 (and
waited 10 minutes), and it's still not working.

Do you have any other suggestions? Thanks.

 

[Accesshelp]

Niek,

By the way, I am using Excel 2003, and I have also tried to use that Excel
file in another computer. I had the same problem.

Thanks.

 

[Niek Otten]

Try Find-and-replace, replacing all "=" signs (equal signs) by "=". Yes,
replace equals sign by equals sign (for the formula sheet)

It wouldn't do any harm to do that for tha data sheet as well

 

[Accesshelp]

Niek,

Thank you very much for continuing to help me.

As you instructed, I replaced the "=" signs on the Report worksheet, and it
seems to work. However, when I press F9, it freezes. So I do the replace
("=" signs) on Data worksheet, and it freezes.

Could you please educate why we have to replace the = signs? What do you
think the problem is?

I don't know this information would make any different. The formula in the
Data worksheet are array formula (e.g. {(......)}). In the Report worksheet,
there are 3 columns, and all 3 columns are vlookup. The first column is
vlookup straight to the data, and the last two columns are vlookup to the
array formula in the Data worksheet.

For the first column being vlookup straight to the data, after I did the
replace on = on the Report worksheet, the first column populates with data.
The last two columns do not populate anything.

Thanks.

 

[Niek Otten]

<Could you please educate why we have to replace the = signs? What do you
think the problem is?>

That is the same as rebuilding the dependency tree.

If you wish, you can mail me your workbook. I will not open it if there are
any macros in it.
I will report anything I might find back to this group.

 

[Accesshelp]

Niek,

As much as I would like to send the file to you, unfortunately, I can't.
Could you help me without the file?

It looks like the problem is on the array formula in the Data worksheet.
When I do the replace on = sign, I can see that the formula start to
calculate and populate the value. Then it stops.

Thanks.

 

[Accesshelp]

Niek,

What (or why) is the purpose of rebuilding the dependency tree? Do I have
to do the rebuilding every signle time when I use the file?

Thanks.

 

[Niek Otten]

<What (or why) is the purpose of rebuilding the dependency tree?>

This is an emergency procedure for the very rare case that Excel has messed
up its calculation chain. Happens really very, very rarely.
Apparently something else is going on with your file.

If the fourth argument of the VLOOKUP is FALSE, it can become a real
time-consumer. If you happen to use Excel2007 and use VLOOKUP to search a
whole column, and the argument can't be found, Excel can seem to get to a
halt.
In that case, consider searching a smaller range, sort the columns and do
two lookups with the fourth argument omitted (or TRUE); one in the key
column so you can check that it is there, and a second one (if it is there)
to retrieve the result column. This can be up to a few thousand times
faster.

For lots of information about Excel performance, visit Charles Williams'
site:

www.decisionmodels.com

Don't hesitate to post again in this same thread if you still have problems

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Accesshelp]

Niek,

Thank you very much for your helps. I have my problems solved.

The problem was due to the bad data that I have in the Data worksheet. That
is why, the calculation stopped at the first colum with bad data. I was able
to solve the problem because of the replacement of = signs. I did one
formula at a time, and one of the formula would not let me to pass by.

Thanks again. You are a genius. Happy New Year!

 

[Niek Otten]

Thanks for the feedback. I'm really glad you got it solved.

 

[Max]

The problem was due to the bad data that I have in the Data worksheet.

That
is why, the calculation stopped at the first colum with bad data

Can you post a bit more detail on the "bad" data to enrich the newsgroups'
experience? Thanks

 

[Max]

The bad data that I had were -0's (negative zeros). After I turned

all -0's
into 0's, it worked perfectly

Thanks for feeding back. This is new to me. Presume those negative zeros are
text numbers? How did you correct these to normal zeros?