Creating a Sliding Commission Scale in Excel

TheDub 

I returned to the problem you posed.  She solutions are so far from the OP requirements that I hesitated to post, but there is an important point that modern Excel supports problem solving which is far closer to programming than it is to traditional spreadsheet authoring on the grid.

The commission problem is simply the evaluation of points on a piecewise-linear function expressed in terms of a sequence of threshold values at which the slope changes.  The inverse problem is also that of evaluating a piecewise-linear function but with different thresholds and slopes.  To solve the new problem, I first Curried the Commissionλ function

COMMISSIONλ
= PWLINEARλ(Threshold, Percentage);


PWLINEARλ
= LAMBDA(thresholdValues, slopes, LAMBDA(values,
    LET(
        limit, SORT(VSTACK(DROP(thresholdValues, 1), values)),
        SUMPRODUCT(limit-thresholdValues, slopes)
    )
));

The worksheet formula then requires one to evaluate the commission applicable at each sales threshold and combine the values obtained with the inverse of the commission rates

Worksheet formula
= LET(
    thresholdCommission,  MAP(Threshold, COMMISSIONλ),
    salesRequired,        MAP(target, PWLINEARλ(thresholdCommission, 1/Percentage)),
    HSTACK(salesRequired, COMMISSIONλ(salesRequired))
  )

A massive change, very little of which is visible from the worksheet.