I have the following data:
Income | Percentile |
1 | 0.002486 |
2 | 0.003284 |
3 | 0.004051 |
4 | 0.004174 |
5 | 0.006552 |
6 | 0.009528 |
7 | 0.014147 |
8 | 0.017845 |
9 | 0.022156 |
10 | 0.027112 |
11 | 0.035168 |
12 | 0.050051 |
13 | 0.068986 |
14 | 0.088641 |
15 | 0.108235 |
16 | 0.128765 |
17 | 0.151796 |
18 | 0.174628 |
19 | 0.204993 |
...... which continues up to the 100th percentile
The percentile numbers are not regular so I want to create an extra row (in the right numerical location) to find the income number for the 50th percentile on Excel I would use the following formula, how would I translate this into alteryx?
I then want to create a new column which has a few separate irregular formulas in it some of which rely on numbers from column B - I presume this would be through using the multi-row formula?
1. For the last row in the column I want to calculate them using the calculated 50th percentile number multiplied by other numbers I already have
2. For the 50th percentile row in the new column I want to be able to input a number I already have
3. For the first row and all the interim cells I want to calculate them in relation to either bottom and mid or mid and top point and also in proportion to the figures in column A
I have attached an excel with the formulas I'd use in Excel to do it
Thanks for the help
Solved! Go to Solution.
Hi @MX,
An interesting challenge. Ended up pulling out each of the "special" calculation rows separately to pull out each "cell" needed for the formulas. Last Formula tool calculates the New Column from these "cells". Some of the new fields are created are to rename them to your Excel formulas so you can trace the calculations. I put annotations in the workflow where I though it may need explanation beyond just looking at the tools. You can also hard code the known input for the 100th Percentile formula, but I brought it in from a text field in case this needs to change.
Thank you so much for your help!
When I open the workflow it looks slightly different to your screenshot and like it's missing the final two containers - would it be possible to upload the full workflow?
Thanks!