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.

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Aug 24, 2025
=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.

 

Resources