Forum Discussion
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 using in the calculator to get the production multiplier may not return an exact value that matches the above data. It could return 1.32. I would need a formula that looks for the closest value to 1.32 and also has a rate of .14 and return the commission structure result. In this example, I would want the commission value of 0.08 returned. Is that possible?
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.
5 Replies
As variant
=LET( CurrentRate, 0.14, CurrentMult, 1.32, diff, ABS(CurrentMult - ProductionMult), XLOOKUP( MIN(diff) & "|" & CurrentRate, diff & "|" & Rate, CommissionStructure) )=SMALL(IF((A2:A12=F1)*(B2:B12<=G1)*(LARGE(IF(B2:B12<=G1,B2:B12),1)<=B2:B12),C2:C12),1)An alternative for legacy Excel could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021 or Excel 2024.
Here's an option that doesn't use FILTER.
=TAKE(SORT(ABS(A2:C12-HSTACK(F2,F1,0)),{1,2}),1,-1)Another approach could be-
=INDEX(SORT(FILTER(B2:C12,(A2:A12=F1)*(B2:B12<=G1)),1,-1),1,2)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.
