Calculated Graphs now support conditional logic using the IF function. This allows you to perform different calculations depending on whether a condition is met.
Example: Sales Commission
Suppose you want to calculate a salesperson's commission based on the number of deals closed.
In this example:
G1(Graph 1) = Number of deals closedG2(Graph 2) = Total sales amount
IF(G1 >= 7, G2 * 0.15, G2 * 0.10)
The formula means:
If the value in Graph 1 is 7 or greater, the commission is 15% of total sales.
Otherwise, the commission is 10% of total sales.
Real-Life Scenario
A sales manager wants to reward high-performing salespeople with a higher commission rate.
Deals Closed | Total Sales | Commission |
8 | $20,000 | $3,000 (15%) |
5 | $20,000 | $2,000 (10%) |
Using conditional logic, businesses can automate calculations for commissions, bonuses, incentives, performance ratings, and many other business rules directly within Calculated Graphs.
Simulating AND / OR Logic
Calculated Graphs do not currently support dedicated AND and OR functions. However, you can achieve the same result by using multiple IF statements.
Simulating AND
To return a specific value only when both Graph 1 and Graph 2 are greater or equal than the value specified:
IF(G1 >= 90, IF(G2 >= 50000, G2 * 0.3, 0), 0)
This means:
If
G1is greater or equal than 90, checkG2.If
G2is also greater or equal than 50000, calculate a 30% commission.Otherwise, return
0.
Real-Life Example: AND Logic
A manager wants to award a team bonus only when both targets are achieved:
Customer Satisfaction Score is greater than 90 (
G1)Revenue is greater than $50,000 (
G2)
Customer Satisfaction | Revenue | Result |
95 | $60,000 | $18,000 |
85 | $60,000 | 0 |
95 | $40,000 | 0 |
85 | $100,000 | 0 |
In this example, the team receives the bonus only when both customer satisfaction targets and revenue are achieved.
Simulating OR
To return a specific value when either Graph 1 or Graph 2 is greater than the value specified:
IF(G1 > 50, G2 * 0.3, IF(G2 > 50000, G2 * 0.3, G2 * 0.1))
This means:
If
G1is greater than 50, calculate a 30% commission.Otherwise, check
G2.If
G2is greater than 50,000, calculate a 30% commission.If neither condition is met, calculate a 10% commission.
Real-Life Example
A manager wants to track whether a sales team met at least one of two targets:
New customers above 50 (
G1)Revenue above $50,000 (
G2)
Using the OR example above, the graph calculates 30% commission when either target is achieved and 10% when neither target is met.
New Customers | Revenue | Commission |
60 | $20,000 | $6,000 (30%) |
5 | $70,000 | $21,000 (30%) |
20 | $40,000 | $4000 (10%) |