Avatar of agwalsh
agwalsh

asked on 

Calculating tiered commission based on plan name and amount

in the file, how do I set up formula so that depending on what plan is chosen e.g. CPVP and the amount of the sale a percentage is chosen? e.g. for  plan CPVP and amounts 0 to 100000 for % commission is .06 (E4). For the plan FFVP it's .045 (E9). I've experimented with Vlookups (using true - which has given me the correct % for amounts ) and if functions (I tried if with the and function and vlookup but that didn't work) but I want it to be dynamic so that I can easily add plans etc. I'm suspecting some variety of Sumproduct...It's going to be linked to a list  where people will be picking the list from a name. Thank you as always.
commission_calcs.xlsx
Microsoft Excel

Avatar of undefined
Last Comment
agwalsh

8/22/2022 - Mon