Need help on a formula for tiered calculation

clara777

Hi , I am trying to put a formula in Alteryx for tiered charges.

Basically the formula below is from excel. How can i replicate to Alteryx?

IF(C2<=250, B2*0.2%, IF(AND(C2>250,C2<=750),B2*0.195%, IF(AND(C2>750, C2<=1500), B2*0.19%, B2*0.185%)))

Where C2 is a running total trade value

and trade value is B2. I am trying to find the total charge 3.084

I have simplied a table to show 

Trade value	Running Total	Charge
50	50	0.1
100	150	0.2
300	450	0.585
120	570	0.234
200	770	0.380
200	970	0.38
50	1020	0.095
600	1620	1.11
1620		3.084
Trade value	Rate
More than 250	0.20%
Between 250 and 750	0.195%
Between 750 and 1500	0.190%
Above 1500	0.185%

IraWatt

Hey @clara777,

Pretty interesting Excel Formula. First thing to note is the syntax for a Excel IF is the same as an Alteryx IIF statment. Second in Alteryx you refer to columns with column names within square brackets. Finally you will have to times numbers by numbers not by percentages so they will have to be converted. I ended up with this:

IIF([Running Total]<=250, [Trade value]*0.002,
IIF([Running Total]>250 AND [Running Total]<=750,[Trade value]*0.00195,
IIF([Running Total]>750 AND [Running Total]<=1500, [Trade value]*0.0019, [Trade value]*0.00185
)))

Although I am not sure how you get your final result of 3.084 applying the rules you made:

Example workflow attached.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20Expressions

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

Workflow.yxmd