6 Replies Latest reply on Jun 3, 2015 12:18 PM by dansan500

# Calculation using multable fields as alternate formula

### Title

Calculation using multable fields as alternate formula

### Post

I have tried using an IF statement for this but I can't make it work:

It is a commission calculation

If the price is <\$150 then Price*30% = Commission   there is a setup field for the consignment customer that contains these fields:

Price Level < 150 - this would be the figure entered for that consignment customer

Price Level > 151 - this would be the figure entered for that consignment customer

Percent < - this would be the figure entered for that consignment customer

Percent >  - this would be the figure entered for that consignment customer

Now what I tried to do using the IF statement was:

IF the PRICE is < 150 then use the calculation for price level 1

IF the PRICE is >151 then use the calculation for price Level 2

But I could't get it to work.. Can you help, it's probably simple and I just don't know it...

• ###### 1. Re: Calculation using multable fields as alternate formula

I don't understand this part of your description:

Price Level < 150 - this would be the figure entered for that consignment customer

Price Level > 151 - this would be the figure entered for that consignment customer

Going by your example calculation, this would be one value, not two, the \$150 shown in your first example.

It seems like you need:

If ( Price < Price Level ; Price * PercentLT ; price * PercentGT )

This assumes that the two percent fields store percent in decimal format. 50% would be stored as 0.5.

• ###### 2. Re: Calculation using multable fields as alternate formula

we usually get from the consignee numbers like this:

If you sell something that is priced under (SELLING PRICE) then we will give you 50% percent commission

If you sell something that is priced over (SELLING PRICE) then we will give you 30% percent commission.

I need for program to make that distinction and then do the formula

• ###### 3. Re: Calculation using multable fields as alternate formula

Exactly. But "Selling Price" is then exactly the same value in both of those sentences correct? If they were different, you'd need three percent commission rates instead of two. The third rate would be for prices greater than the first selling price and less than than the second selling price.

• ###### 4. Re: Calculation using multable fields as alternate formula

that is correct, the Selling Price is one price that comes from the sales order. Depending what that number is will determined the formula used to get the answer.

• ###### 5. Re: Calculation using multable fields as alternate formula

Yet in your original example you had a place for two price levels. Given your last post here, my example in my original post should be sufficient to do what you have requested.