Dynamic Production Data Calculation

[javedmati]

Hi,

In Column A2 to A31 I have date for April 2018
In Column B2 to B31 I am entering Daily Production Data

Monthly production target is 720,000 (i.e. 24,000 per day in case of April 2018).

If the production on 1st April is say 10,000 (Instead of 24,000) then the required production on daily basis for 2nd April to 30th April should automatically change to 24,482.75 (i.e. (720,000 - 10,000)/29) in order to meet the monthly target of 720,000.

Can someone please help ?

Thanks a lot in advance

 

[sheetspread]

Excel 2010
	A	B
2	4/1/2018	10000
3	4/2/2018	24482.76
4	4/3/2018	24482.76
5	4/4/2018	24482.76
6	4/5/2018	24482.76
7	4/6/2018	24482.76
8	4/7/2018	24482.76
9	4/8/2018	24482.76
10	4/9/2018	24482.76
11	4/10/2018	24482.76
12	4/11/2018	24482.76
13	4/12/2018	24482.76
14	4/13/2018	24482.76
15	4/14/2018	24482.76
16	4/15/2018	24482.76
17	4/16/2018	24482.76
18	4/17/2018	24482.76
19	4/18/2018	24482.76
20	4/19/2018	24482.76
21	4/20/2018	24482.76
22	4/21/2018	24482.76
23	4/22/2018	24482.76
24	4/23/2018	24482.76
25	4/24/2018	24482.76
26	4/25/2018	24482.76
27	4/26/2018	24482.76
28	4/27/2018	24482.76
29	4/28/2018	24482.76
30	4/29/2018	24482.76
31	4/30/2018	24482.76
Sheet6

Cell Formulas
Range	Formula
B3	=(720000-SUM($B$2:B2))/(2+30-ROW())

 

[javedmati]

Hi,
I can’t use the formula. Because I need to manually enter every day’s production data which will overwrite the formula.

I believe it has to be a VP script. After entering data for one day I call the MACRO to do the calculations.

 

[sheetspread]

Just type the production amounts over each formula and the rest will adjust.

 

[javedmati]

Dear Sir,

Let's say I manually enter production data from B2 to B10. I understand that from B11 to B31 it will automatically update values however if I need to make any changes in the cells B2 to B10 then this will not work since the formula is overwritten.

Please help me

 

[sheetspread]

Excel 2010
	A	B
1
2	4/1/2018	10000
3	4/2/2018	9873
4	4/3/2018	11254
5	4/4/2018	19654
6	4/5/2018	28733
7	4/6/2018	22343
8	4/7/2018	14871
9	4/8/2018	11000
10	4/9/2018	6587
11	4/10/2018	29811
12	4/11/2018	27793.7
13	4/12/2018	33243
14	4/13/2018	27490.96
15	4/14/2018	27490.96
16	4/15/2018	27490.96
17	4/16/2018	132546
18	4/17/2018	19987.03
19	4/18/2018	19987.03
20	4/19/2018	19987.03
21	4/20/2018	19987.03
22	4/21/2018	19987.03
23	4/22/2018	19987.03
24	4/23/2018	19987.03
25	4/24/2018	65432
26	4/25/2018	12412.87
27	4/26/2018	12412.87
28	4/27/2018	12412.87
29	4/28/2018	12412.87
30	4/29/2018	12412.87
31	4/30/2018	12412.87
Sheet6

Cell Formulas
Range	Formula
B12	=(720000-SUM($B$2:B11))/(2+30-ROW())

Note the mix of entered values with formulas. It will work even if there are breaks and you can paste the formula back into the overwritten cells as desired. What am I missing? Do you want all the formula results to be the same no matter where? Right now the earlier formulas are weighted more because they represent a lower deduction from the 720k goal.

 

[sheetspread]

Same as above but with the formula cells highlighted in blue:

Excel 2010
	A	B
1
2	4/1/2018	10000
3	4/2/2018	9873
4	4/3/2018	11254
5	4/4/2018	19654
6	4/5/2018	28733
7	4/6/2018	22343
8	4/7/2018	14871
9	4/8/2018	11000
10	4/9/2018	6587
11	4/10/2018	29811
12	4/11/2018	27793.7
13	4/12/2018	33243
14	4/13/2018	27490.96
15	4/14/2018	27490.96
16	4/15/2018	27490.96
17	4/16/2018	132546
18	4/17/2018	19987.03
19	4/18/2018	19987.03
20	4/19/2018	19987.03
21	4/20/2018	19987.03
22	4/21/2018	19987.03
23	4/22/2018	19987.03
24	4/23/2018	19987.03
25	4/24/2018	65432
26	4/25/2018	12412.87
27	4/26/2018	12412.87
28	4/27/2018	12412.87
29	4/28/2018	12412.87
30	4/29/2018	12412.87
31	4/30/2018	12412.87
Sheet6

Cell Formulas
Range	Formula
B12	=(720000-SUM($B$2:B11))/(2+30-ROW())