Forum Discussion
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 ...
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.
As variant
=LET(
CurrentRate, 0.14,
CurrentMult, 1.32,
diff, ABS(CurrentMult - ProductionMult),
XLOOKUP( MIN(diff) & "|" & CurrentRate, diff & "|" & Rate, CommissionStructure)
)