Calc: Cap/limit a calculation to a certain goal/threshold

[lyubo]

Hello,

I need to adjust values from cells but I need the formula to stop adjusting after a set goal or threshold. The trickiest part is that the formula needs to be able to partially adjust a single entry as most of the times a single entry exceeds the limit.

I’ve composed a simplified sample spreadsheet (below). What I need is what’s in the “Capped” sheet but implemented with formula/s, not manually altered. I would appreciate any help as I can’t seem to be able to figure it out myself.

Uncapped_Capped.ods (16.5 KB)

---

EDIT: I’ve been told that my sample spreadsheet is abstract and confusing. I wanted to make it as simple as possible but I can see how that falls short. Here is the sample updated to be more specific and clear.

For the specific example in the table below: if column A contains entries in kilograms and column B contains the price adjustment for the respective entries, the price adjustment formula needs to be applied to only the first 50 kg. of the total sum of the entries in column A.

Uncapped_Capped_Upd.ods (21.4 KB)

[JohnSUN]

Welcome!
Perhaps the formula
=A2*MAX(MIN(($E$2-SUM($B$1:$B1))/A2;0.25);0)
will do the trick for you. An example of use on a Capped_Correct sheet - Uncapped_Capped.ods (19.9 KB)

[lyubo]

The formula does stop the adjustment after the entry which reaches the limit but it does not partially adjust said entry if the total sum with it exceeds the limit.

[JohnSUN]

Yes, I already understood - I checked the wrong column that you need. The corrected formula looks like this:
=$E$2*MIN(MAX($F$2-SUM($A$1:$A1);0);A2)

[lyubo]

Thank you, this does the job.