• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to reduce heap of formulas in a sheet

Dear Excel Ninjas

Please find the attached sheet with the formulation which I'm using to maintain our company details since 2015 to till date.

The formulas from S4 to DK4 and it is continued up to S9600 to DK 9600. This excel sheet is getting stuck frequently with these heap of formulas.

Is there any way to use setup calculations top-down in excel to reduce the calculation timing.

Thanks in advance.
 

Attachments

  • Heap of Formulas.xlsx
    146 KB · Views: 4
Hi ,

Can you copy the formulae when the workbooks are open , so that the long pathname is not a part of the formula , and then upload the workbook with the formulae ?

Narayan
 
Dear Narayan

Please check the attached file which is original and without any data for your kind perusal.
 

Attachments

  • Master Sheet.xlsm.xlsx
    895.8 KB · Views: 6
Just a thought: why do you recalculate historical data? Can't you replace by values to already reduce the number of formulae used for 2015-2017? Or a divide and concur kind of approach.
e.g. If you are using a version that comes with PQ/Get and Transform you could first save a single workbook for each of the years, including current year with formulae and then consolidate each via a PQ. Then you can perform some extra analysis via PQ, pivots, formulae...
 
Just a thought: why do you recalculate historical data? Can't you replace by values to already reduce the number of formulae used for 2015-2017? Or a divide and concur kind of approach.
e.g. If you are using a version that comes with PQ/Get and Transform you could first save a single workbook for each of the years, including current year with formulae and then consolidate each via a PQ. Then you can perform some extra analysis via PQ, pivots, formulae...


Yes, I do remove formulas in the years of 2015 and 2016. But it's not reducing as much speed.

It is really helpful if you can send a model file or link that shows how can I keep year by year data in separate sheet and analysis in a pivot table from all the sheets?

Note: I am using MS Office 365 2016.

Thanks in Advance.
 
What is the purpose of Reducing the Number of Formulas?
What are you hoping to achieve?

Recently I asked for a solution where I had several hundred formulas in rows like:
=MIN($C11-SUM($E11:E11),F$6-SUM(F$10:F10))
and there were 240 columns of data.
They were then summed up and divided by a number in each column to get an average weighted answer per column.

The solution presented to me here was:
=IFERROR((SUM(IFERROR(EXP(LN(IF(SUM($E6:F6)-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1))<_r,SUM($E6:F6)-MMULT(N(ROW(_r1)>=TRANSPOSE(ROW(_r1))),N(+_r1)),_r))),)*$D11:$D32)-SUM(N(+$E6:E6)*N(+$E7:E7)))/F$6,)

Now I can explain to you how the top formula works in a few sentences.
It is very clear and logical and it is how you would solve the problem manually.

The lower formula will require several pages of notes and then you may still struggle.

Now in my case I had a very special need to simplify the structure of the worksheet and hence the complex formula was used, but generally as Narayan pointed out, Excel has 1 Million + rows and 32,000+ columns for a reason.

Don't be afraid of using them...

As Guido suggested reducing historical data to values, or summaries can be used to simplify large data.

If the issue is that the worksheet is slow, I would first get all the data into one workbook, as external links between workbooks are also very slow.

Data organisation is one of the biggest issues that people face and poor data layouts can kill the performance of Excel.

If you were to re-ask the question as to : I have this data and I want to do this with it, you may get a different answer to what you are asking here ?
 
Hi ,

See if this is some reduction.

Probably if your workbook had had all of the data in it , it would make it easier to understand the calculations that are being done.

Having a complete blank workbook with just the formulae in it , is making it difficult to visualize what is happening.

If the workbook were complete in all respects , we could see the effect of any change that is made ; at present , there is no way we can see how a change has made an impact on the recalculation time.

Narayan
 

Attachments

  • Master Sheet.xlsm.xlsx
    977.9 KB · Views: 3
Another means, if VBA is an option. Create a macro to run the calculations once on each cell that needs the calculations done. This could be a last resort, as it does have some drawbacks. Other ideas listed above may be more elegant if these can be implemented instead.
 
Hi ,

See if this is some reduction.

Probably if your workbook had had all of the data in it , it would make it easier to understand the calculations that are being done.

Having a complete blank workbook with just the formulae in it , is making it difficult to visualize what is happening.

If the workbook were complete in all respects , we could see the effect of any change that is made ; at present , there is no way we can see how a change has made an impact on the recalculation time.

Narayan

Dear Narayan

Please check the below link to dropbox excel file because here it is not accepted more than a 1mb file.

In the attachment, I have added dummy information of 10 to 15 rows value for your study and comments.

Note:
In your last reply attached file is not opening here.

https://www.dropbox.com/s/xy4w0o8m221dvow/Heap formulas.xlsm.xlsx?dl=0

Thanks in Advance
 
Another means, if VBA is an option. Create a macro to run the calculations once on each cell that needs the calculations done. This could be a last resort, as it does have some drawbacks. Other ideas listed above may be more elegant if these can be implemented instead.

Hi

Thanks for your suggestion

I don't know the macro program to run the calculation. If it is possible please make a sample sheet and send to me according to my requirements.

Original file link is below with dummy information for your information
https://www.dropbox.com/s/xy4w0o8m221dvow/Heap formulas.xlsm.xlsx?dl=0
 
Hi ,

See this file.

You have to let us know whether there is any change.

https://www.dropbox.com/s/l51vre3u29i4m50/Narayan Heap formulas.xlsm 1111.xlsx?dl=0

Narayan

Please check your two files updated today. One is 1.1MB which is updated on 7:16 AM another one is 11.2MB which is updated 11:38 AM.

Just 8000 rows increased this much memory?

If possible, Can we convert master data sheet to table format to reduce the memory?

My intention is to reduce the file size to avoid the hanging or stuck while calculating each times entry.

At present, I'm using a manual calculation to avoid interruption while entering the data. Otherwise, each and every entry is taking 5 to 10 seconds to calculate.
 
Hi ,

The worksheet has 8000 rows and nearly 100 columns with formulae in them.

Data entry is in at most 15 columns !

I have no idea of what calculations are being done in this worksheet , and whether all of them are necessary.

Converting a range to a table will not achieve anything in this context.

The only way to reduce file size and re-calculation time is to eliminate formulae.

The only way to eliminate formulae is to study your application , and if required , redesign the workbook itself.

That will require a complete understanding of what this workbook is doing.

Probably if you explain in enough detail , someone else may help you in this.

Narayan
 
Dear Narayan

Please check the below details for your study

Column A to I is production details

Column J to R is Sales details

Column U to BH is the formulation of each product (Data has to pick from two different sheets such Temporary and permanent as attached)

Column S, T and BI to BQ is the calculation of the cost of Raw materials, transportation, margin etc..

Column BR to DB is each production quantity * Each product raw materials used as per formulation Column U to BH (To calculate the raw materials consumption)

Column DC to DE is the helper column for the slicer in Pivot table

Column DH to DF is the helper column the sheet COA as attached here.

Column DI and DJ is the helper column to pick the formulations from Temporary and permanent sheet to Master data.

And the last column DK is to cross-check the production quantity and the consumption of raw materials by formulation.

I hope the details are enough to understand the output of sheet and you can guide accordingly.

https://www.dropbox.com/s/5h3sn3yn9y04bhe/Model file.xlsx?dl=0

Expecting your positive reply. Thanks in Advance.
 
Back
Top