Forum Discussion

vfernandes7's avatar
vfernandes7
Copper Contributor
Apr 16, 2023

Save Fail - Formula longer than 8192 characters

I have to calculate the comission for each sales. Each Sales Person, selling to a specific Customers, a specific Product, at a specific price, has it comission calculate in different way.

So I have listed all conditions, and concatenated inside an AND formula to check if that specific condition is matched later, if so, it returns how the comission is calculated.

 

 

Then I TEXTJOIN all this conditions into a IFS formula, so for each line, I can check the comission.

 

Then I paste this huge formula into the sales sheets, to calculate each comission. But now I can't save my file because this formula exceeds 8192 characters.

 

 

How can I handle this problem? Is there another easier way to check several conditions to calculate the comission?

excel
Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 17, 2023

    vfernandes7 

    You can use a combination of IF, VLOOKUP, and CONCATENATE functions to create a single formula that checks several conditions to calculate commission.

    Here are the steps:

    1. Create a new workbook in Excel and name it “Commission Calculator”.
    2. Type Sales Person in cell A1, Customer in cell B1, Product in cell C1, Price in cell D1, and Commission Rate in cell E1.
    3. Enter the sales person name for the first transaction in cell A2. In cell B2, enter the customer name. In cell C2, enter the product name. In cell D2, enter the price of the product. In cell E2, enter the commission rate.
    4. In cell F2, enter the following formula: 
    5. =IF(AND(A2="Sales Person 1",B2="Customer 1",C2="Product 1",D2=100),VLOOKUP(D2,{0,0;100,0.05;200,0.1;300,0.15},2),IF(AND(A2="Sales Person 2",B2="Customer 2",C2="Product 2",D2=200),VLOOKUP(D2,{0,0;100,500;200,1500;300,3000},2),""))&IF(AND(A3="Sales Person 3",B3="Customer 3",C3="Product 3",D3=300),VLOOKUP(D3,{0,0;100,0.05;200,0.1;300,0.15},2),IF(AND(A3="Sales Person 4",B3="Customer 4",C3="Product 4",D3=400),VLOOKUP(D3,{0,0;100,500;200,1500;300,3000},2),""))

     

    This formula checks if Sales Person is “Sales Person 1” and Customer is “Customer 1” and Product is “Product 1” and Price is 100 then it uses the first VLOOKUP function to calculate commission otherwise it checks if Sales Person is “Sales Person 2” and Customer is “Customer 2” and Product is “Product 2” and Price is 200 then it uses the second VLOOKUP function otherwise it returns an empty string.

     

    I hope this helps! 

Resources