Excel seep up calculating time

Hi,

I'm developing a tool for a client and it's taking about 2 to 3 seconds to calculate after any change on it.

I don't know much about how excel calculates, for example, when I change some value, does it calculate all the sheets or just the one I'm on?

So, I have a database and my formulas looks like this:

Please Login or Register  to view this content.

I thought this was the better way to get the data because it kind of "transform" the database into a binary.
I thought maybe the sheet "Resultado" was the one ******* everything up, because it has almost 400 cells doing this calculations, but when I deleted it, nothing changed.

Mardelle.xlsx

My goal is to make calculation as fast as possible.

I was introduced to PowerPivot, it would help me in this case? I don't know much about it, but if the solution is it, I can learn.

I have a i7-3770K 3.5GHz with 16GHz of memory, my client probably have something worst.

Thank you all for helping.

Thank you for the help, but I may face NA() values on the sum range.
If I have NA() values on range, I need to sum the other values. That's why I'm using ={SUM(IF(ISERROR(E212:P212);0;E212:P212))}

As a matter of fact I think I can remove something from the formula.

I don't know what you mean by "helper columns in the "dados" sheet"

Loja is the "Store", my client has 8 stores and he can choose which store data will show.
Ano is "Year", which is also another "filter".

On sheet "Resultado", which is kind of a Income Statement, my client can choose by "Store" and "Year".

What about PowerPivot, does anyone know something about it?

Originally Posted by MrShorty

I would suggest that the most effective way to speed up this spreadsheet is to rethink these array formulas and see if you can eliminate or reduce this duplicated effort. For example, I would probably try to move the "loja" and "ano" parts of the conditional sum into helper columns in the "dados" sheet.

I tried to remove some part of the formula but it actually got worst.

Please Login or Register  to view this content.

So, the part I deleted was the part responsible for returning "" when there was no data under the filters, by deleting it, it would return 0 instead, which is not ideal because there are some values that are 0.

In case of a new store, for example, the data could start in the middle of the year, if that was when the store started its activities. In that case the formula helps.
But when I'm asking for some data that I know exist, that part is useless.

Anyway, I prefer to leave it the way it was. Don't know where else I can rethink formulas.

Originally Posted by Pepe Le Mokko

Yes sorry about that. Perhaps the AGGREGATE function then =AGGREGATE(9,6,e212:p212)

That worked and it seems to be making a difference. Thank you, didn't know this function.

Well, on my Database I don't have values that can be calculated, like Income, Profit, etc.
I'm guessing if I added this values on my database I would save a lot of calculations on my formulas.

I'll try that.

Thank you. Usually I use syntax like Table1[DATA]:[DATA]] because I will use the same formula on different columns.