how to get numbers to stop calculating

Question

Level 1

10 points

how to get numbers to stop calculating

I have made a very large sheet to track baseball stats. This is the first time I've ever done anything like this and had no idea what I was getting into. I got a sheet made up for every team(162 rows and about 75 columns each), another to put in daily information(almost 5000 rows with 12 columns, which is used as a reference for all of the individual team sheets), and another reference sheet with misc info used again by all teams. I was so excited with my work. All I have to do on a daily basis is put in how many runs each team scored and everything I could possibly want to know is calculated for me! The problem is that with each change I make comes an excruciating wait for it to calculate. Anywhere from 15-60 minutes.

So what I'm wondering is, is there a way to get it to stop calculating at a certain point so that I can do the things that I want and then let it do all of it's calculations when I am done?

Also is there a different program that I should be using? Almost every row and column has a formula in it to get all of the info that I need. I thought it was my computer because most of my memory was being used so I upgraded that and now I'm only using about 1/3 and the wait time has barely gone down.

Thank you

Posted on Apr 23, 2016 10:39 AM

Reply

Answer 1

t quinn

Level 5

7,063 points

Apr 23, 2016 10:57 AM in response to aswad12

Hi aswad,

5000 rows is a lot for Numbers to handle, I am told. I don't think that is the resason your tables are taking so very long to refresh. It does sound from your description that you have a lot of duplication of effort going on within your tables. I am forgetting right now what causes numbers to recalculate, I am sure someone will stop by and help us out.

If you have the same formula in similar tables that will be slowing things down. You can get by with just making that calculation once. Say you decide to do a calculation in Table 1::A2 that you want in Table 2::A2. The simple way to get that value into Table 2::A2 is to type "=" into that cell, navigate to Table 1 and click the cell you want the value from. this will bring over that value and the calculation will be done only once.

quinn

Reply

Answer 2

aswad12 Author

Level 1

10 points

Apr 23, 2016 10:42 AM in response to aswad12

Also is there a way to copy the text from a cell and not the formula that created the text? I am thinking I can make multiple copies with each doing less and less work by copying results but when i try that the formula copies over as well.

Reply

Answer 3

aswad12 Author

Level 1

10 points

Apr 23, 2016 11:43 AM in response to t quinn

Unfortunately it all pulls information for each individual team so whatever info is being pulled on one page isn't being pulled from another. The way it is set up now, all I do is copy and paste the date that each team plays onto a score sheet which is why it is almost 5000 rows. 30 teams*162 games. Then once I input their runs for the day, it pulls that onto their team sheet as well as their opponents. Each team home sheet is set up that once i copy paste their schedule for the year from outside website, it calculates everything I could possibly want to know about that team for the season. win streaks, losing streaks, home stands and road trips, etc. So with the exception of runs scored and opponent win %, nothing is one more than 1 sheet. That is why I can't "=" in multiple tables. I actually started doing that, but then I had to go to each opponents page and get their row numbers for their scores and %. The problem is getting up that sheet having to switch pages 50 times for 30 teams was painful. So I figured I'd spend the time upfront letting the computer do the work for me. And that isn't working either. Apple store told me to try FileMaker but I don't know if that will be any better than hat I have now.

Reply

Answer 4

SGIII

Level 8

37,622 points

Apr 23, 2016 5:28 PM in response to aswad12

aswad12 wrote:

is there a way to copy the text from a cell and not the formula that created the text?

Yes, select the cells, command-c to copy, and Edit > Paste Formula Results to paste the values without the formula. When you do that those values won't be recalculated each time you make a change in your document, perhaps making it less sluggish.

A key factor here is what kind of formulas you are using, to do what kind of calculations. Can you give any details? Some formulas use a lot more processing power and recalculate much more frequently than others.

SG

Reply

Answer 5

aswad12 Author

Level 1

10 points

Apr 23, 2016 7:39 PM in response to SGIII

Thank you SGIII. That worked for me to copy and paste without the formulas.

The formulas are nested if,and, or statements. That makes up about 1/2 of the formulas and the other half are split between vlookups and index match. So basically once I paste the schedule in the sheet, it lets me know which series are 2, 3, or 4 game series. If its home or away. Pretty much anything that can have an outcome of a game. There are about 30 columns per team that are blank until I input the teams runs for that given day and then it fills in the next row until the next day and repeat. It is pretty complex to me although I'm fairly new to this.

Someone told me that every time I make a change of any sort that it runs through all of the formulas again. And that is why it takes so long. I was under the impression that only the cells I changed and their affected formulas would have to calculate.

Reply

Answer 6

SGIII

Level 8

37,622 points

Apr 23, 2016 7:46 PM in response to aswad12

Some functions, sometimes called "volatile" functions, recalculate whenever you make a change anywhere in the sheet. Most functions I understand only recalculate as needed in their chain of calculations.

It sounds as if you have a pretty complicated structure if you're using nested ifs, etc. Is there a possibility, as quinn has suggested, of simplifying your structure. If you want to post specifics someone here may be able to give you pointers.

SG

Reply

Answer 7

t quinn

Level 5

7,063 points

Apr 23, 2016 9:13 PM in response to aswad12

Hi aswad,

It may also help to separate your tables so you input table only carries data that you enter and your formulas are in another.

If it is possible to replace the nested IFs taht would be a good thing.

quinn

Reply

Answer 8

aswad12 Author

Level 1

10 points

Apr 25, 2016 4:37 PM in response to t quinn

ok thank you everyone. I think what I may end up doing is doing one set of formulas for the schedule. copy/paste the results onto the team sheets so the remaining formulas only calculate the daily items. And then have a third set of sheets which I copy/paste weekly all of the daily items.

Reply

Answer 9

SGIII

Level 8

37,622 points

Apr 25, 2016 4:52 PM in response to aswad12

Without specifics it's hard to comment on how well Numbers can do the job for you after you adopt that workflow. You mentioned Filemaker earlier. It can handle masses of data for sure, but it would probably be harder to "program" the formulas (again, it's hard to say without specifics). Excel almost certainly can handle the job, and it would probably be more similar to what you already have in Numbers.

SG

Reply