Stop equations updating dynamically when table is sorted/filtered

[drDanRyan]

Hi!

I'm having an issue with a large spreadsheet of raw data I'm using for my dissertation. I'm trying to do some analysis of the data (averages, t-tests, standard deviation etc.) in a second sheet. I create an equation to conduct, for example, a t-test looking at a particular variable, however when I move onto my next calculation, and filter/sort the table so I can see the measurements I want, my previous calculations are changing. I assume this is because the rows are moving around.

Is there any way to fix this? I've had a workaround suggested to me, where I copy the values needed for an equation onto a new sheet before calculating, however this would be quite time consuming since I'm looking at conducting 60-70 equations. Any ideas?

Thanks in advance.

[RudiS]

Welcome to the forum

If you switch Excel's calculation mode into manual it will stop your formulas from recalculating each time you sort.
The calc mode button should be on the Formulas ribbon...to the right. (I don't have a Mac, so I cannot confirm)

[MrShorty]

however this would be quite time consuming since I'm looking at conducting 60-70 equations. Any ideas?

It will probably be difficult to give you any specific help without a copy of the spreadsheet (confidential data removed).

Another aspect of this -- especially if time is important -- is how best to use that time. As tedious as it is to copy and paste 60-70 sets of data, sometimes I find it easier and faster to do this kind of thing by copy and paste. Other times, I prefer to figure out the appropriate combinations of VLOOKUP(), INDEX(), LARGE(), SMALL(), etc. that will programmatically perform the desired data extraction operation. I'm reading between the lines here, but only you would know if your skill level with Excel's "database" type functions is good enough to allow you to programmatically extract the different data sets for analysis or if it will ultimately be easier/faster to simply copy and paste.

[drDanRyan]

Welcome to the forum

If you switch Excel's calculation mode into manual it will stop your formulas from recalculating each time you sort.
The calc mode button should be on the Formulas ribbon...to the right. (I don't have a Mac, so I cannot confirm)

This seems to be working so far. Thanks for the fix!

For reference, it's the same option on the mac version. Formulas ribbon -> settings on the far right.