Here is an image of the Excel table that I have started with:
Here is an image of the solved sheet. The solution was calculated by Python and it is exactly following the given criteria.
The image was made in Google Sheets, because I transferred it there, in order to make it easy for you to access the data.
The link to the table: https://docs.google.com/spreadsheets/d/1nFQEIc2ELeS5G99vezTuhaJCbmWmvB_wPkWL3dIbR1s/edit?usp=sharing
You can see the criteria that I must follow. Until now, I have calculated these fields with Python, however, now I have to pass this task to my colleague who doesn't know Python, and asked me to make a solution for her in Excel. This is a small table, so it would be easy to do it by hand here, however, the real table has around 500 rows.
I need to modify the values in D2:E27 in order to make all values in the range of K2:L3 greater than 60%. I can use any Excel version (365, 2021, etc.), however, I would like a no-code solution for my colleague. Is it possible? If so, how?
to solve, it need 4 similar steps :
type: A & Amount of LS
type: A & Amount of FL
type: B & Amount of LS
type: B & Amount of FL
I will talk about first case and the others will be the same first calculate some guiding values :
=SUMIFS($C$2:$C$27;$B$2:$B$27;"A")without condition of amount=SUMIFS($C$2:$C$27;$B$2:$B$27;$J2;D$2:D$27;">0")with condition of amount=F30*0,6-F31to know the range will be add of the areawe need to add two helper columns will be deleted or hide at the end column help1 will has equation
=if(and(not(D2>0);B2="A";C2>20;C2<35);C2;"")notice the values 20 and 35 in this equation changing (try and error) until the sum in cell f28 is greater than the guiding calculation in cell f32column LS value will has equation
=if(not(F2="");$K$10/COUNT($F$2:$F$27);D2)then take column LS value copy and paste values only in Amount of LS
and there is a link of a copy of the data you shard with those equations
copy of the data you shard