Hi,
Could you possibly help me in figuring out the correct function to calculate compensation based on the following assumptions?
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
Thank you very much.
Purely for information, you could also write that like this:
=G2*IF(OR(C2={"Service Fee","Credit"}),8%,4%)