We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

alteryx Community

# Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

## Need help on a formula for tiered calculation

5 - Atom

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%

4 REPLIES 4
17 - Castor

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%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

11 - Bolide

@IraWatt, this would be because the last row (1620) is the final running total and the final charge (3.084) is a summation of all  of the charges before it. If what you're looking for is the last row then you will need to use the Summarize tool.

The attached workflow uses @IraWatt's formula.

17 - Castor

Ah thank you @chukleswk ! It makes sense now 😄

5 - Atom

Thanks for all ur help. I will try out!!

Labels