Hey Everyone -
I'm trying to create a analysis workflow where I compare vendors (A thru D in my examples) prices over the same locations (states AK - AR). Where I'm stuck is creating a single formula that will calculate the % variance from the lowest price for each vendor without having to create a unique formula for each vendor. My list of vendors is constantly changing and not a short list so I'm trying to avoid having to re-create this for each data set. Is there anyway to make a formula/macro that would dynamically add a column for each vendors % variance to the lowest price? Or In my example would I be stuck making these 4 formulas (A % vs Lowest Value, B % vs Lowest Value, etc).
Here is an example below with my vendors (A - D) and what I'm trying to accomplish in bold.
Competitors | |||||||||
State | A | B | C | D | Lowest Value | A % vs Lowest Value | B % vs Lowest Value | C % vs Lowest Value | D % vs Lowest Value |
AL | 100 | 125 | 110 | 105 | 100 | 0% | 25% | 10% | 5% |
AK | 75 | 70 | 80 | 90 | 70 | 7% | 0% | 14% | 29% |
AR | 45 | 30 | 20 | 15 | 15 | 200% | 100% | 33% | 0% |
AZ | 90 | 80 | 70 | 60 | 60 | 50% | 33% | 17% | 0% |
Solved! Go to Solution.
Hi @bebrown,
If you wanted to keep it truly dynamic you would want to go down the route of pivoting, then calculating percentages before finally re-pivoting back to it's original form:
If this solves your issue please mark the answer as correct, if not let me know! I've attached my worklfow for you to download if needed.
Regards,
Jonathan
Thanks Jonathan!