13 Replies Latest reply: Mar 18, 2015 8:36 AM by Rupert Cavendish

Expressions Issue

Hi,

Im trying to create a expression that states the following

If the listprice divided by the salesprice is more or equal to 112.5 %  then the amount above should be mutilplide by 1.30 if not then multipled by 1.0

ive started but got stucked pretty fast.

If(((Sum(lägstapris)/Sum(Försäljningspris))<=('1,125')), * '1.30' )

Im kinda in a stress in this matter.

/André

• Re: Expressions Issue

Try this:

If((Sum(lägstapris)/Sum(Försäljningspris))>=1.125,Sum(lägstapris)/Sum(Försäljningspris)) * 1.30,Sum(lägstapris)/Sum(Försäljningspris))

You do not need to multiply by 1 as will give you the same result.

• Re: Expressions Issue

Hi Andre,

Is it what you are looking for?

IF((LP/SP)>='112.5%',((LP/SP)-1125)*1.30,(1125-(LP/SP))*1.0)

Regards

KC

• Re: Expressions Issue

I think I took your statement  " amount above " in wrong sense.

IF(sum(LP)/sum(SP)>='112.5%',sum(LP)/sum(SP)*1.30,sum(LP)/sum(SP))

Regards

KC

• Re: Expressions Issue

Hi

Try this:

If(Num((Sum(lägstapris)/Sum(Försäljningspris)),'#,##0%')<='112.50%',(Sum(lägstapris)/Sum(Försäljningspris))*1.30,

(Sum(lägstapris)/Sum(Försäljningspris))*1.0)

Hope that works

Regards

Av7eN

• Re: Expressions Issue

Hi,

=if((sum(listprice)/sum(salesprice))>=112.5,(sum(listprice)/sum(salesprice))*1.30,(sum(listprice)/sum(salesprice))*1.0)

• Re: Expressions Issue

Hi,

Try

If((Sum(lägstapris)/Sum(Försäljningspris))>=1.125,

(Sum(lägstapris)/Sum(Försäljningspris))*1.30,

(Sum(lägstapris)/Sum(Försäljningspris))

)

regards

• Re: Expressions Issue

Hi,

What do you mean by "amount above"? I am assuming it as sum(ListPrice)/sum(SalesPrice).

Could you please try using it:

if(

(sum(ListPrice)/sum(SalesPrice))*100 >=112.5,
(sum(ListPrice)/sum(SalesPrice))*1.3,

(sum(ListPrice)/sum(SalesPrice))

)

• Re: Expressions Issue

lägstapris * (If(lägstapris/Försäljningspris <=1.125)), 1.30, 1))

should do (please check decimal-symbol to be either "." or ",".

HTH Peter

• Re: Expressions Issue

Hi,

Thx for fast response, im gonna try the above, just to clearify, its only the amount above 1,125 that should be multiplied, so the expression should give the result,

lets for say that the salesprice is 50000 the list price is (100%)40000 then the first (112,5 %) 45000 should be multiplied with 1.0 the rest (12,5 %) 5000 should be multiplied by 1.30 giving a result of 51500.

so the extra gain for the salemans bonus is 1500 since he sold the kitchen 25% above list price.

Hope this sorted some of the thoughts

• Re: Expressions Issue

this is a new bonus system that give the salesman 30% extra when they salesamount is 12,5 % above listprice.

• Re: Expressions Issue

So the formula is

(SP - LP * 1.125) * 1.3?

Peter

• Re: Expressions Issue

No, since the difference between salesprice and listprice renders a percentage depending on that percentage some action will be taken, if the salesman have a percentage of 1,125 meaning he has sold for 12,5 % over the listprice, this doesent render anything extra for the salesman except the normal bonus, but if the sales would have been 1.25 % this means that the salesman should have an extra of 30 % on the amount above 1.125.

SP 50000 LP 40000 diffrence 10000 equal to 25 % above LP 12,5 % of the above results in 5000 that he should have an extra of 30 % on meaning that the salesman will get on that kitchen 1500 % on bonus base sales, since the kitchen in the bonus calculation will have a value of 51500 instead of the normal 50000.

• Re: Expressions Issue

I think that this is what you are looking for ....

If(((Sum(lägstapris)/Sum(Försäljningspris)) <= 1.125,

Sum(Försäljningspris) + ((Sum(lägstapris) - Sum(Försäljningspris)) * 1.3,

Sum(Försäljningspris)

)