Formula off a Dynamic List

[bebrown]

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%

[ImadZidan]

Hello @bebrown ,

Are you thinking of something like this.

Just an idea.

Hope you find it helpful.

[echuong1]

I believe you're looking for something like this?

[Jonathan-Sherman · Accepted 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

[bebrown · Accepted solution]

Thanks Jonathan!