Calculate a SRP price depending the part type and the values of the CCR Price

[Jadreani]

Hello,

I need some help regarding a calculation of a SRP price's part.

The first condition is the part type of the part.

For each part type, I must calculate the SRP price based on the CCR price value with multiple comparison operators (equal or less than ** price) and as well a coefficient.

For example:

If the part type is "BA0" AND

• if the CCR Less than or equal to 40, then the SRP Price will be 250
• If the CCR price is between 41 and 99, then the SRP Price will be 400
• if the CCR price is between 100 and 200, then the SRP Price will be 500
• if the CCR prise is greater than 200, then the SRP price will be calculcated based on CCR price * 3.5

If the part type is "HB0" AND

• if the CCR Less than or equal to 20, then the SRP Price will be 100
• If the CCR price is between 21 and 99, then the SRP Price will be 200
• if the CCR price is between 100 and 150, then the SRP Price will be 300
• if the CCR prise is greater than 150, then the SRP price will be calculcated based on CCR price * 5

Please find below a data sample:

Thanks a lot in advance for your kind help.

Article Reference	Part type	CCR
BA0195	BA	50
BA0196	BA	75
BA0244	BA	100
BA0253	BA	389
BA0254	BA	100
HB0067	HB0	125
HB0076	HB0	100
HB0079	HB0	50
HB0080	HB0	230
HB0081	HB0	110

[Arul · Accepted solution]

@Jadreani ,

I hope the below calculated column would help you. Try and let me know.

SRP Price = 
SWITCH(TRUE(),AND(SRP[Part type]="BA",SRP[CCR]<=40),250,
AND(SRP[Part type]="BA",and(SRP[CCR]>=40,SRP[CCR]<=99)),400,
AND(SRP[Part type]="BA",and(SRP[CCR]>=100,SRP[CCR]<=200)),500,
AND(SRP[Part type]="BA",SRP[CCR]>200),SRP[CCR]*3.5,
AND(SRP[Part type]="HB0",SRP[CCR]<=20),100,
AND(SRP[Part type]="HB0",and(SRP[CCR]>=21,SRP[CCR]<=99)),200,
AND(SRP[Part type]="HB0",and(SRP[CCR]>=100,SRP[CCR]<=150)),300,
AND(SRP[Part type]="HB0",SRP[CCR]>150),SRP[CCR]*5
)

Thanks,

Arul

[Jadreani]

Dear Arul,

I'm so grateful you helped me. Thanks very much, so appreciated!

Kindest regards,

Jean-Yves

[Arul]

Hi @Jadreani ,Thank you.

If it solves your problem do a thumps up and accept the solution.