Excel hanging or stuck while calculating a heap of formulas

Anbuselvam

Board Regular
Joined
May 10, 2017
Messages
52
https://www.dropbox.com/s/gfp9tfe6k2kc61r/Model sheet.xlsm.xlsx?dl=0

Dear Excel Ninjas

The above link excel sheet with the formulation which I'm using to maintain our company details since 2015 to till date. (Pivot table and Pivot charts not included as it is a model sheet created with dummy entries)

In the attached file I have entered only 13 rows of data with all the formulations. Actually, it is having more than 8000 rows thus it is hanging while calculating.

Each column in details:

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 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.

Here I request you to suggest alternate formulas or formats to get the same output in each column as well as reduce file size and reduce the hanging issue while calculating.

Thanks in Advance

Sincerely Yours
Anbuselvam K
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
the dropbox attachment appears scrambled - can you possibly simplify what you are doing - show us some raw data - pretend if necessary - and how you want to manipulate it..................
 

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,005
the drop box link is also very slow - if I am to try and help you, please make a new spreadsheet with say 10 rows of raw data, at this stage no pivots etc
 

Anbuselvam

Board Regular
Joined
May 10, 2017
Messages
52
Sl.No.DateProduct NameBatch NoKMT / ChemanolProd. Qty. in Ltr.Qty. in KGDateInvoice numberCustomer NameLocationSelling Price / Ltrs. Desp. Qty. in Ltr.CostSales ValueRM1RM2RM3RM4RM5RM6RM7RM8RM9RM10RM11RM12RM13RM14RM15RM16RM17RM18RM19RM20RM21RM22RM23RM24RM25RM26RM27RM28RM29RM30RM31RM32RM33RM34RM35RM36RM37Maufacturing costTransportSP gravity rangeRM Cost / kgTotal RM CostTotal Sales Cost W/O MC & TraTotal Sales CostTotal Manufacturing costTotal Transportation CostMarginTotal Production Cost W/O MC & TraTotal Production CostRM1RM2RM3RM4RM5RM6RM7RM8RM9RM10RM11RM12RM13RM14RM15RM16RM17RM18RM19RM20RM21RM22RM23RM24RM25RM26RM27RM28RM29RM30RM31RM32RM33RM34RM35RM36RM37Production DateMonth SalesSales DayHelper Column QuantityHelper Column Batch NumberHelper Column DatePermanentTemporaryCross Check
119-Mar-15
clip_image001.png
clip_image001.png
clip_image001.png
clip_image001.png
clip_image001.png
Product 1

<tbody>
</tbody>
1234Type 1100010557-May-1510002AUAE1.5001000.000.3211500.0075012001300000000000000000000000000000000.20.11.0550.02020.8920.89320.89200.00100.001179.1120.89320.889791.250.00126.600.000.00137.150.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002015-032015-057011055.00
27-May-15Product 11235Type 1100010557-May-1510003BUAE1.5001000.002.8531500.0075012001300000000000000000000000000000000.20.11.0552.4202552.892552.892852.89200.00100.00-1352.892552.892852.889791.250.00126.600.000.00137.150.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002015-052015-057011055.00
34-Apr-15Product 11236Type 1100010557-May-1510004AUAE1.5001000.000.3211500.0075012001300000000000000000000000000000000.20.11.0550.02020.8920.89320.89200.00100.001179.1120.89320.889791.250.00126.600.000.00137.150.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002015-042015-057011055.00
47-May-17Product 21237Type 1100010801-Jan-000.7890.00630000012001200130000000000000000000000000.20.11.080.453488.940.000.00200.00100.000.00488.94788.940680.400.000.000.000.000.00129.600.000.00129.600.000.00140.400.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002017-053000-011021080.00
515-May-17Product 21238Type 1100010801-Jan-000.7890.00630000012001200130000000000000000000000000.20.11.080.453488.940.000.00200.00100.000.00488.94788.940680.400.000.000.000.000.00129.600.000.00129.600.000.00140.400.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002017-053000-011021080.00
615-Jun-17Product 21239Type 1100010801-Jan-000.7890.00630000012001200130000000000000000000000000.20.11.080.453488.940.000.00200.00100.000.00488.94788.940680.400.000.000.000.000.00129.600.000.00129.600.000.00140.400.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002017-063000-011021080.00
731-Mar-16Product 31240Type 1100012007-May-1610008&10020AUAE1.5001000.000.8231500.00420330012001300000000000000000000000000000.20.11.20.436522.66522.66822.66200.00100.00677.34522.66822.661504.000.00396.000.000.00144.000.000.00156.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002016-032016-057101200.00
84-Apr-16Product 31241Type 1100012007-May-1610009BUAE1.5001000.000.8231500.00420330012001300000000000000000000000000000.20.11.20.436522.66522.66822.66200.00100.00677.34522.66822.661504.000.00396.000.000.00144.000.000.00156.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002016-042016-057101200.00
94-May-16Product 31242Type 1100012007-May-1710010AUAE1.5001000.000.8231500.00420330012001300000000000000000000000000000.20.11.20.436522.66522.66822.66200.00100.00677.34522.66822.661504.000.00396.000.000.00144.000.000.00156.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002016-052017-057101200.00
1029-Apr-17Product 41243Type 1100012307-May-1710011BUAE1.5001000.000.8711500.00580001700120013000000000000000000000000000.20.11.230.464570.97570.97870.97200.00100.00629.03570.97870.973713.400.000.000.00209.100.000.00147.600.000.00159.900.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002017-042017-057201230.00
111-May-17Product 41244Type 1100012307-May-1810012AUAE1.5001000.000.8711500.00580001700120013000000000000000000000000000.20.11.230.464570.97570.97870.97200.00100.00629.03570.97870.973713.400.000.000.00209.100.000.00147.600.000.00159.900.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002017-052018-057201230.00
125-May-17Product 41245Type 1100012307-May-1810013BUAE1.5001000.000.8711500.00580001700120013000000000000000000000000000.20.11.230.464570.97570.97870.97200.00100.00629.03570.97870.973713.400.000.000.00209.100.000.00147.600.000.00159.900.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002017-052018-057201230.00
137-May-18Product 41246Type 1100012307-May-1810014AUAE1.5001000.001.4661500.00580001700120013000000000000000000000000000.20.11.230.9481165.831165.831465.83200.00100.0034.171165.831465.826713.400.000.000.00209.100.000.00147.600.000.00159.900.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.002018-052018-057201230.00

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="9"><col><col><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="3"><col><col><col><col><col span="2"><col><col><col><col span="2"><col><col><col span="9"><col><col><col span="3"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col span="3"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,892
Messages
5,598,693
Members
414,254
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top