SOLVED

Calculate two percentages from a single cell

%3CLINGO-SUB%20id%3D%22lingo-sub-3200932%22%20slang%3D%22en-US%22%3ECalculate%20two%20percentages%20from%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3200932%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20calculate%20commission%20based%20on%20the%20chart%20below.%20I%20have%20the%20following%20ifs%20function%20added%20%3DIFS(D5%26gt%3B9999999%2C%200.06*C5%2C%20D5%26gt%3B4999999%2C%200.14*C5%2C%20%22TRUE%22%2C%200.1*C5).%20However%20if%20a%20value%20in%20column%20D%20reaches%20one%20of%20the%20commission%20thresholds%20(%245%2C000%2C000%2C%20%2410%2C000%2C000%3B%20or%20%2415%2C000%2C000)%20I%20want%20the%20formula%20to%20calculate%20the%20difference%20between%20the%20threshold%20to%20calculate%20the%20two%20commission%20values.%20In%20other%20words%2C%20in%20the%20example%20below%2C%20the%20%245%2C000%2C000%20commission%20threshold%20is%20reached.%20I%20want%20the%20%24493%2C203%20(D12-E12)%20*%20.14%20and%20%2446%2C797%20(C12%20-%20number%20above)%20*%20.1.%20The%20formula%20also%20needs%20to%20adapt%20so%20if%20the%20threshold%20is%20met%20in%20July%20or%20September%2C%20the%20formula%20calculates%20in%20the%20new%20row.%20Thanks%20in%20advance%20for%20your%20help!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22salbert_54_0-1645562889929.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350475i98FB0A9C4090C602%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22salbert_54_0-1645562889929.png%22%20alt%3D%22salbert_54_0-1645562889929.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3200932%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3201394%22%20slang%3D%22en-US%22%3ERe%3A%20Calculate%20two%20percentages%20from%20a%20single%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3201394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1314994%22%20target%3D%22_blank%22%3E%40salbert_54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFS(D5%26gt%3B9999999%2C%0AIF(D4%26gt%3B9999999%2CC5*0.06%2C(D5-10000000)*0.06%2B(C5-(D5-10000000))*0.14)%2C%0AD5%26gt%3B4999999%2C%0AIF(D4%26gt%3B4999999%2CC5*0.14%2C(D5-5000000)*0.14%2B(C5-(D5-5000000))*0.1)%2C%0A%22TRUE%22%2C%0A0.1*C5)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIs%20this%20what%20you%20are%20looking%20for%3F%20The%20calculation%20for%20the%26nbsp%3B%3CSPAN%3E%2415%2C000%2C000%3C%2FSPAN%3E%26nbsp%3Bthreshold%20can%20be%20added%20accordingly%20with%20the%20respective%20percentage.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I'm trying to calculate commission based on the chart below. I have the following ifs function added =IFS(D5>9999999, 0.06*C5, D5>4999999, 0.14*C5, "TRUE", 0.1*C5). However if a value in column D reaches one of the commission thresholds ($5,000,000, $10,000,000; or $15,000,000) I want the formula to calculate the difference between the threshold to calculate the two commission values. In other words, in the example below, the $5,000,000 commission threshold is reached. I want the $493,203 (D12-E12) * .14 and $46,797 (C12 - number above) * .1. The formula also needs to adapt so if the threshold is met in July or September, the formula calculates in the new row. Thanks in advance for your help!

salbert_54_0-1645562889929.png

 

 

2 Replies
best response confirmed by salbert_54 (New Contributor)
Solution

@salbert_54 

=IFS(D5>9999999,
IF(D4>9999999,C5*0.06,(D5-10000000)*0.06+(C5-(D5-10000000))*0.14),
D5>4999999,
IF(D4>4999999,C5*0.14,(D5-5000000)*0.14+(C5-(D5-5000000))*0.1),
"TRUE",
0.1*C5)

Is this what you are looking for? The calculation for the $15,000,000 threshold can be added accordingly with the respective percentage.

@Quadruple_Pawn Yes this is perfect! Thank you for the help!