Large data set and complexish formulae causing system to be slow

Hello,

I have what seems to be a larger than usual file 42MB, with the main sheet containing 34000 rows and 162 columns. The data is plain data with no conditional formatting. I add about 500 rows every week.?*

To analyse the data, I use a separate sheet in the same workbook, with a bunch of formulae. All theses formulae, 40 in total, are conditional formulae referencing whole columns. Here is an example:?*

=LET(
A,FTSAdvanced!$AE:$AE,
B,FTSAdvanced!$AI:$AI,
C,FTSAdvanced!$BB:$BB,
D,FTSAdvanced!$BF:$BF,
E,FTSAdvanced!$BX:$BX,
H,FTSAdvanced!$EH:$EH,
I,FTSAdvanced!$EQ:$EQ,
J,FTSAdvanced!$CX:$CX,
K,FTSAdvanced!$CO:$CO,
L,FTSAdvanced!$DG:$DG,
M,FTSAdvanced!$D:$D,
MAX(FREQUENCY(IF(
(IF(OR($B$2="",$C$2=""),1,(A>=$B$2)*(A<=$C$2)))*
(IF(OR($B$3="",$C$3=""),1,(B>=$B$3)*(B<=$C$3)))*
(IF(OR($B$4="",$C$4=""),1,(C>=$B$4)*(C<=$C$4)))*
(IF(OR($B$5="",$C$5=""),1,(D>=$B$5)*(D<=$C$5)))*
(IF(OR($B$6="",$C$6=""),1,(E>=$B$6)*(E<=$C$6)))*
(IF(OR($B$9="",$C$9=""),1,(H>=$B$9)*(H<=$C$9)))*
(IF(OR($B$10="",$C$10=""),1,(I>=$B$10)*(I<=$C$10)))*
(IF(OR($B$11="",$C$11=""),1,(J>=$B$11)*(J<=$C$11)))*
(IF(OR($B$12="",$C$12=""),1,(K>=$B$12)*(K<=$C$12)))*
(IF(OR($B$13="",$C$13=""),1,(L>=$B$13)*(L<=$C$13)))*(M=$A$1)
*(FTSAdvanced!$FL:$FL="N"),ROW(FTSAdvanced!$FL:$FL)),
IF(
(IF(OR($B$2="",$C$2=""),1,(A>=$B$2)*(A<=$C$2)))*
(IF(OR($B$3="",$C$3=""),1,(B>=$B$3)*(B<=$C$3)))*
(IF(OR($B$4="",$C$4=""),1,(C>=$B$4)*(C<=$C$4)))*
(IF(OR($B$5="",$C$5=""),1,(D>=$B$5)*(D<=$C$5)))*
(IF(OR($B$6="",$C$6=""),1,(E>=$B$6)*(E<=$C$6)))*
(IF(OR($B$9="",$C$9=""),1,(H>=$B$9)*(H<=$C$9)))*
(IF(OR($B$10="",$C$10=""),1,(I>=$B$10)*(I<=$C$10)))*
(IF(OR($B$11="",$C$11=""),1,(J>=$B$11)*(J<=$C$11)))*
(IF(OR($B$12="",$C$12=""),1,(K>=$B$12)*(K<=$C$12)))*
(IF(OR($B$13="",$C$13=""),1,(L>=$B$13)*(L<=$C$13)))*(M=$A$1)
*(FTSAdvanced!$FL:$FL<>"N"),
ROW(FTSAdvanced!$FL:$FL)
))))

My concern is that I need to add more formulae to be able to analyse the data properly but excel is taking more and more time to calculate - 30 to 40 seconds. I am not an excel pro, as you would have guessed, and I would really appreciate some help to make excel work more efficiently.?*

As I said, there isn't any conditional formatting, VBA or volatile formulae. Calculating is in manual mode, and everything is saved on my machine.

My questions are:

How could I make those formulae more efficient?

Is there an alternative to referencing the whole columns? Should I use dynamic ranges - index? If so will this help with speed?

Should I convert my data into a huge table? Would this make excel work easier?

I run excel 365, on an inter mac 16gb. I can't access power pivot or anything other than what I already have.

Thanks

Excel help 2504.xlsx

A guess... referencing whole columns will be the most likely cause. If you have 40000 rows, Excel may waste a lot of time calculating 1,000,000++ rows over, and over again

Probably convert it to a table and use structured Table references. However the whole thing looks unwieldy!! My tiny brain can't possibly figure out what it's doing... but I'd bet you a shiny penny that there's a better way to do it!!

Any chance of getting a SMALL sample sheet?

Small = 10-20 rows... not thousands... to illustrate the PRINCIPLE...

An example of limiting the row count without converting the data to a Table.

Formula:

Please Login or Register  to view this content.

Ideally, you should use the same (fully populated) column to determine the last row ( lr ) for all ranges. I've just used AE in the absence of any data.