Home

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

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

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).

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


@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


 

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
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies