Blanks returned instead of zeros
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Rob, I've added that as a solution. Amazing how versatile Excel is. Shame it is limited in memory.
My sheets can take up 3-4GB and that's when it blows up.
My sheets can take up 3-4GB and that's when it blows up.
I assume you've already seen options of using 64 bit and saving as xlsb to help with size.
ASKER
Yes using 64bit Rob, but forgot about saving as xlsb
what approx % size reduction would it be ?
Thanks
what approx % size reduction would it be ?
Thanks
A .xlsb file will often be half the size of .xlsx files. They open and save noticeably faster than .xlsx files as an added benefit.
ASKER
Thanks Brad. Are there any downsides using xlsb format ? For example I use the Solver Add On quite extensively and complex formulas over tens of thousands of rows and dozens of columns but no VBA.
Solver and complex formulas should be no problem. When the file is open, it has the same structure as a .xlsx. For this reason, I would expect no difference in recalc speed after you make an edit to your file.
If you use third-party apps, they may not support the .xlsb file format. Tableau is a good example of an app that is .xlsb challenged. FWIW, Tableau is an expensive app used for Business Intelligence visualizations, so I'd be surprised if you use it or ever want to do so in the future.
If you use third-party apps, they may not support the .xlsb file format. Tableau is a good example of an app that is .xlsb challenged. FWIW, Tableau is an expensive app used for Business Intelligence visualizations, so I'd be surprised if you use it or ever want to do so in the future.
ASKER
I am on a path to learn SPSS which I can copy and past to from Excel. I expect I can save the xlsb file back as an xlsx one.
I believe IBM SPSS does not support reading or writing files with .xlsb file extension. If so, you will need to convert your files back to .xlsx by opening them in Excel and saving them with the other file extension.
Alternatively, split into two files:
1) Raw data, could be excel or maybe even a database (Access etc)
2) Computing file, linked to raw data
1) Raw data, could be excel or maybe even a database (Access etc)
2) Computing file, linked to raw data
ASKER
Thanks Brad and Rob for your comments.
ASKER
I have fixed that at source by returning zeros and hiding them and then using conditional
formatting on the target cells by selecting white font colour.
Works a treat
Cheers
Ian