Reduce Excel file size due to formula entered in 100 rows X 8000 columns

[KartikiJ]

Hi,
I need to enter below formula in 100 rows X 8000 columns.

=IF(AND(QJ$1>=$F8,QJ$1<=$G8,NOT(ISNUMBER(MATCH(QJ$1,'Resource Leave Plan'!$A$4:$A$83,0))),IF($I8<>"",NOT(ISNUMBER(MATCH(QJ$1,rarr($I8),0))),TRUE)),$I8,1)

the reference range changes with every cell. this formula is used for conditional formatting

The file size is around 27MB now. how do I reduce it?

 

[neeraj2050]

Hi,

Did you try to compress the file (Right click on the file and you will see the option)
or If you are working into the older version of excel then simply save (save as) your file into newer version may reduce the size of your file.

Note: Please keep the backup of original file before trying any of the aforementioned methods.

Regards
Neeraj Kumar Agarwal

 

[vletm]

Could You use VBA; one formula not 800000 formulas?

 

[Hui]

Have you saved the file as an Excel Binary file type *.xlsb?

 

[jeffreyweir]

KartikiJ: It's a bit hard to optimize your formula or suggest alternatives without a sample file that gives context. Any chance you can post one?

That said, you should be able to replace this bit:
NOT(ISNUMBER(MATCH(QJ$1,'Resource Leave Plan'!$A$4:$A$83,0)))
...with this:
ISNA(MATCH(QJ$1,'Resource Leave Plan'!$A$4:$A$83,0))

And also replace this bit:
NOT(ISNUMBER(MATCH(QJ$1,rarr($I8),0)))
...with this:
ISNA(MATCH(QJ$1,rarr($I8),0))

Is rarr a UDF? If so, what does it do?

If you post more context about what you're trying to achieve, then we can probably optimize the hell out of this.