function to calculate compensation|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hello,

As you mentions, the net value is calculated as:
= Order Value - ((One Time Cost * Quantity of Terminals) + (Monthly Cost * (Nr of Months * Quantity of Terminals)))
But only if the Charge Type = Service Fee, otherwise the Net Value = Order Value.

In order to calculate the Compensation, which should be 8% if the Charge Type is either Service Fee or Credit, for remaining two types (Starting Fee and Branding Pack) it is to be 4%, you can use a nested formula with IF and OR functions.
= IF(OR(Charge Type = "Service Fee" , Charge Type = "Credit") , Net Value * 8% , Net Value * 4%)

You can write this in another way, skipping the OR function. The + sign acts just like the OR function. If using this, just remember you need to enclose each and every condition with parentheses.
= IF((Charge Type = "Service Fee") + (Charge Type = "Credit") , Net Value * 8% , Net Value * 4%)

So, by using row 2 in your file as an example, the formula would be like this.
= IF(OR(C2 = "Service Fee" , C2 = "Credit") , G2 * 8% , G2 * 4%)

Br,
Anders