Forum Discussion

sbennett2345's avatar
sbennett2345
Copper Contributor
Aug 22, 2025
Solved

Formula help - not sure where to start

I have this data:    I want to create a sort of calculator where based on the rate and production multiplier, the result is the commission structure value. The problem is, the calculation I am ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Aug 22, 2025

    Let's say the rate 0.14 is in J1 and the production multiplier 1.32 is in J2.

    The corresponding commission structure is

    =LET(Dif, ABS(J2-FILTER($F$2:$F$12, $E$2:$E$12=J1)), 
     MinDif, MIN(Dif), 
     Com, FILTER($G$2:$G$12, $E$2:$E$12=J1), 
     XLOOKUP(MinDif, Dif, Com))

    You can adjust the ranges of course.

     

HansVogelaar's avatar
HansVogelaar
MVP
Aug 22, 2025

Let's say the rate 0.14 is in J1 and the production multiplier 1.32 is in J2.

The corresponding commission structure is

=LET(Dif, ABS(J2-FILTER($F$2:$F$12, $E$2:$E$12=J1)), 
     MinDif, MIN(Dif), 
     Com, FILTER($G$2:$G$12, $E$2:$E$12=J1), 
     XLOOKUP(MinDif, Dif, Com))

You can adjust the ranges of course.

 

Resources