Excel hanging or stuck while calculating a heap of formulas

[Anbuselvam]

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

 

[oldbrewer]

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

 

[Anbuselvam]

https://www.dropbox.com/s/gfp9tfe6k2kc61r/Model sheet.xlsm.xlsx?dl=0

Please check the link with the data of near 6000 rows and the excel sheet memory is above 8MB.

My intention is to reduce the calculation time by changing the existing formulas or something else, But the results remain same in each column.

If required you can replace or change any columns but the output should be same.

 

[oldbrewer]

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]

Sl.No.	Date	Product Name	Batch No	KMT / Chemanol	Prod. Qty. in Ltr.	Qty. in KG	Date	Invoice number	Customer Name	Location	Selling Price / Ltrs.	Desp. Qty. in Ltr.	Cost	Sales Value	RM1	RM2	RM3	RM4	RM5	RM6	RM7	RM8	RM9	RM10	RM11	RM12	RM13	RM14	RM15	RM16	RM17	RM18	RM19	RM20	RM21	RM22	RM23	RM24	RM25	RM26	RM27	RM28	RM29	RM30	RM31	RM32	RM33	RM34	RM35	RM36	RM37	Maufacturing cost	Transport	SP gravity range	RM Cost / kg	Total RM Cost	Total Sales Cost W/O MC & Tra	Total Sales Cost	Total Manufacturing cost	Total Transportation Cost	Margin	Total Production Cost W/O MC & Tra	Total Production Cost	RM1	RM2	RM3	RM4	RM5	RM6	RM7	RM8	RM9	RM10	RM11	RM12	RM13	RM14	RM15	RM16	RM17	RM18	RM19	RM20	RM21	RM22	RM23	RM24	RM25	RM26	RM27	RM28	RM29	RM30	RM31	RM32	RM33	RM34	RM35	RM36	RM37	Production Date	Month Sales	Sales Day	Helper Column Quantity	Helper Column Batch Number	Helper Column Date	Permanent	Temporary	Cross Check
1	19-Mar-15	Product 1 <tbody> </tbody>	1234	Type 1	1000	1055	7-May-15	10002	A	UAE	1.500	1000.00	0.321	1500.00	75	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.055	0.020	20.89	20.89	320.89	200.00	100.00	1179.11	20.89	320.889	791.25	0.00	126.60	0.00	0.00	137.15	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2015-03	2015-05	7				0	1	1055.00
2	7-May-15	Product 1	1235	Type 1	1000	1055	7-May-15	10003	B	UAE	1.500	1000.00	2.853	1500.00	75	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.055	2.420	2552.89	2552.89	2852.89	200.00	100.00	-1352.89	2552.89	2852.889	791.25	0.00	126.60	0.00	0.00	137.15	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2015-05	2015-05	7				0	1	1055.00
3	4-Apr-15	Product 1	1236	Type 1	1000	1055	7-May-15	10004	A	UAE	1.500	1000.00	0.321	1500.00	75	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.055	0.020	20.89	20.89	320.89	200.00	100.00	1179.11	20.89	320.889	791.25	0.00	126.60	0.00	0.00	137.15	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2015-04	2015-05	7				0	1	1055.00
4	7-May-17	Product 2	1237	Type 1	1000	1080	1-Jan-00						0.789	0.00	63	0	0	0	0	0	12	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.08	0.453	488.94	0.00	0.00	200.00	100.00	0.00	488.94	788.940	680.40	0.00	0.00	0.00	0.00	0.00	129.60	0.00	0.00	129.60	0.00	0.00	140.40	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2017-05	3000-01	1				0	2	1080.00
5	15-May-17	Product 2	1238	Type 1	1000	1080	1-Jan-00						0.789	0.00	63	0	0	0	0	0	12	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.08	0.453	488.94	0.00	0.00	200.00	100.00	0.00	488.94	788.940	680.40	0.00	0.00	0.00	0.00	0.00	129.60	0.00	0.00	129.60	0.00	0.00	140.40	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2017-05	3000-01	1				0	2	1080.00
6	15-Jun-17	Product 2	1239	Type 1	1000	1080	1-Jan-00						0.789	0.00	63	0	0	0	0	0	12	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.08	0.453	488.94	0.00	0.00	200.00	100.00	0.00	488.94	788.940	680.40	0.00	0.00	0.00	0.00	0.00	129.60	0.00	0.00	129.60	0.00	0.00	140.40	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2017-06	3000-01	1				0	2	1080.00
7	31-Mar-16	Product 3	1240	Type 1	1000	1200	7-May-16	10008&10020	A	UAE	1.500	1000.00	0.823	1500.00	42	0	33	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.2	0.436	522.66	522.66	822.66	200.00	100.00	677.34	522.66	822.661	504.00	0.00	396.00	0.00	0.00	144.00	0.00	0.00	156.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2016-03	2016-05	7				1	0	1200.00
8	4-Apr-16	Product 3	1241	Type 1	1000	1200	7-May-16	10009	B	UAE	1.500	1000.00	0.823	1500.00	42	0	33	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.2	0.436	522.66	522.66	822.66	200.00	100.00	677.34	522.66	822.661	504.00	0.00	396.00	0.00	0.00	144.00	0.00	0.00	156.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2016-04	2016-05	7				1	0	1200.00
9	4-May-16	Product 3	1242	Type 1	1000	1200	7-May-17	10010	A	UAE	1.500	1000.00	0.823	1500.00	42	0	33	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.2	0.436	522.66	522.66	822.66	200.00	100.00	677.34	522.66	822.661	504.00	0.00	396.00	0.00	0.00	144.00	0.00	0.00	156.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2016-05	2017-05	7				1	0	1200.00
10	29-Apr-17	Product 4	1243	Type 1	1000	1230	7-May-17	10011	B	UAE	1.500	1000.00	0.871	1500.00	58	0	0	0	17	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.23	0.464	570.97	570.97	870.97	200.00	100.00	629.03	570.97	870.973	713.40	0.00	0.00	0.00	209.10	0.00	0.00	147.60	0.00	0.00	159.90	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2017-04	2017-05	7				2	0	1230.00
11	1-May-17	Product 4	1244	Type 1	1000	1230	7-May-18	10012	A	UAE	1.500	1000.00	0.871	1500.00	58	0	0	0	17	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.23	0.464	570.97	570.97	870.97	200.00	100.00	629.03	570.97	870.973	713.40	0.00	0.00	0.00	209.10	0.00	0.00	147.60	0.00	0.00	159.90	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2017-05	2018-05	7				2	0	1230.00
12	5-May-17	Product 4	1245	Type 1	1000	1230	7-May-18	10013	B	UAE	1.500	1000.00	0.871	1500.00	58	0	0	0	17	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.23	0.464	570.97	570.97	870.97	200.00	100.00	629.03	570.97	870.973	713.40	0.00	0.00	0.00	209.10	0.00	0.00	147.60	0.00	0.00	159.90	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2017-05	2018-05	7				2	0	1230.00
13	7-May-18	Product 4	1246	Type 1	1000	1230	7-May-18	10014	A	UAE	1.500	1000.00	1.466	1500.00	58	0	0	0	17	0	0	12	0	0	13	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0.1	1.23	0.948	1165.83	1165.83	1465.83	200.00	100.00	34.17	1165.83	1465.826	713.40	0.00	0.00	0.00	209.10	0.00	0.00	147.60	0.00	0.00	159.90	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	2018-05	2018-05	7				2	0	1230.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>