COMPLEX excel with line graph

[sarangvpatil5]

I have this complex problem , (refer screenshot)
please suggest

Its about row 22 and row 31
row 22 is fixed – products that need to be shipped (prediction) per week
row31 is real numbers by user – products those are really shipped per week

Here row 13 is remaining stock real , I.e D13 = C13 – C31 , E13 = D13 – D31 and so on …

row 4 is remaining stock predicted I.e D4 = C4 – C22 , so on

What I want ,

in F31 , there should be automatic value , that is average percentage of previous numbers … I mean , for F31, it should consider , C31, D31, E31 percentage wrt row 22 .
For example , C31/C22 *100 = 358/600*100 = 59.66 , similarly , D31/D22 *100 = 94.16 , similarly E31/E22 *100 = 474 / 800*100 = 59.25 ,
now here the percentage average is 59.66+94.16+59.25 / 3 = 71.02%
now the next value for I,e F31 would be 71.02 percentage of F22 , i.e 71.02 percent of 800 . and so on for g31, h31, so on

Please give a look at column 22 , it have different values ,so the percentage is according to them ,

it should show me the values from F31 , g31, h31 and so on in red font , this shows that , it will be sent in future weeks , but when as a user I will a put value in that column 31 as real quantity that is shipped. Here when I enter that value , I ll make it black fonted ,so that its officially sent … the next weeks should be adjusted as per current and previous numbers , should be in red font
lets say , for example , I gave F31 = 500 , then from g31 , the numbers should change, as now its average percentage would be different in red font

Now the next challenge is .

make LINE graph out of it ,
the graph would be row 22 vs row 31 , the values are predicted and real values per week
so on Y axis --- quantities ,and on X axis – weeks .
but here, ofcourse the row 22 would be same , but row 31 would be change as user will change values per week. But the challenge is , in row 31 , the black font values (actually sent should be in black line in graph) and red font values in spreadsheet should be be red in graph . this mean , if I enter value on 14th week and marked it black font in excel spreadsheet , the graph should show all the weeks till 14th as black and rest should be red , means red from 15th onwards. should change in real time as user gives values.

looks simple but really complex

thanks

 

[Fluff]

For the 2nd time.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: complex problem with line graph
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.