Home

Proper IF function - I can't figure this one out

Highlighted
RobE514
New Contributor

I am trying to figure out a revenue projection and I can't figure this one out. I collect a % of my sales person's commission but IF they reach $12,000 in commission paid I stop collecting the %

 

I know I need the product of the commission x % but how do I say to use the actual total if it is under $12000 but to stop at $12,000 if the total exceeds $12,000?

 

Thanks for the help, I'm stumped 

4 Replies
Highlighted

Does an If statement work here, such that if your commission > $12,000, take the original value?

 

Here's a formula I put together in C2: =IF(A2*B2 > 12000, A2, A2+(A2*B2)) where I have the sale in A2 and a commission percentage in B2.

 

If the total commission is > 12,000, then C2 is just the original sale without commission. If the total commission is < 12,000, then I add the original sale + commission amount (A2 * B2).

Highlighted

Other words you'd like to pay MIN(12000, Sales*Percent), correct?

Highlighted

@Sergei Baklan wrote:

Other words you'd like to pay MIN(12000, Sales*Percent), correct?

 

Actually, I want the maximum company split (take) to be 12,000 from each sales person


 

Highlighted

I tried that but it did not work. Here's a screenshot. In the highlighted cell I am trying to take the $29700 x the company split (that's easy enough) but have it max out at $12,000. So, in the pic below, The commission is F26 and the company split is C37

Capture.JPG

Related Conversations
Make Share function in Edge more useful
HotCakeX in Discussions on
2 Replies
function talking to table storage
donquijote in Compute on
0 Replies
Calculated column help
gopalaraoa in SharePoint on
1 Replies
Excel If Functions
Mfouad2255 in Excel on
10 Replies
Combining IF and INDEX functions
Hans van Deursen in Windows PowerShell on
2 Replies