Formula Help

[vishwa1979]

Dear Team,

I have two sheets. Production Data and Commision Rate.

Based on the same I have to calculate commission against each POLICY_REF.

Login to calculate commission is given below and also in result sheet.

Please help to generate workflow.

POLICY_REF	Business Flag	P_ID	Booking Amount	Commision	Logic to be applied to calculate Commision
OH-O1-21	NB	1001	1110608	37.0	as P_ID is 1001 and Business Flag is NB then as per commission rate sheet for NB rate is 0.05 / 1500 Booking amount
OH-O1-23	REN	1001	1112345	37.1	as P_ID is 1001 and Business Flag is NB then as per commission rate sheet for NB rate is 0.05 / 1500 Booking amount
OH-O1-25	NB	1801	369100	0.5	as P_ID is 1001 and Business Flag is NB then as per commission rate sheet for NB rate is 0.5
OH-O1-27	RENEWAL	1801	208790	o.25	as P_ID is 1001 and Business Flag is NB then as per commission rate sheet for NB rate is 0.25

[ed_hayter · Accepted solution]

Your logic is copied across all rows and is a bit confusing what it means. I have made a start to the flow reshaping and joining so that the production data can be joined to the commission rate. it just needs clarification on the commission formula i think.

If you could be more clear on what the formula is for commission i can take another look

*typo should say left([field], 3)

[ed_hayter · Accepted solution]

Got the numbers but not really scalable solution at present i wouldn't have thought

[vishwa1979]

Hi,

Thank you so much.

I have further modified and got the result which I want.