Max versus Min values across multiple entries

[leebauman]

Hello, I have a sheet with transaction history on hundreds of items. I output indicating the % variance of each item's Max value versus it's Min value. Thank you.

Data sample:

Book7
	A	B	C
1	Item	Transaction ID	Price
2	Apple	A1	$1.00
3	Apple	A2	$1.50
4	Apple	A3	$1.25
5	Banana	A4	$0.50
6	Banana	A5	$0.50
7	Pear	A6	$0.75
8	Orange	A7	$1.00
9	Orange	A8	$2.00
10	Orange	A9	$2.50
11	Orange	A10	$0.50
12	Orange	A11	$8.00
Sheet1

Desired output:

Book7
	A	B
14	Item	Max versus Min % Variance
15	Apple	50%
16	Banana	0%
17	Pear	0%
18	Orange	700%
Sheet1

Cell Formulas
Range	Formula
B15	B15	=(1.5-1)/1
B18	B18	=(8-1)/1

 

[Eric W]

How about:

Book1
	A	B	C
1	Item	Transaction ID	Price
2	Apple	A1	1
3	Apple	A2	1.5
4	Apple	A3	1.25
5	Banana	A4	0.5
6	Banana	A5	0.5
7	Pear	A6	0.75
8	Orange	A7	1
9	Orange	A8	2
10	Orange	A9	2.5
11	Orange	A10	0.5
12	Orange	A11	8
13
14	Item	Max versus Min % Variance
15	Apple	50%
16	Banana	0%
17	Pear	0%
18	Orange	1500%
Sheet7

Cell Formulas
Range	Formula
A15:B18	A15	=UNIQUE(CHOOSE({1,2},A2:A12,(MAXIFS(C2:C12,A2:A12,A2:A12)-MINIFS(C2:C12,A2:A12,A2:A12))/MINIFS(C2:C12,A2:A12,A2:A12)))
Dynamic array formulas.

 

[leebauman]

Yes, that's perfect (realizing I made a mistake in my calculation on the orange item). However, I get strange results. I must be doing something wrong?

Book9
	A	B	C
1	Item	Transaction ID	Price
2	Apple	A1	1
3	Apple	A2	1.5
4	Apple	A3	1.25
5	Banana	A4	0.5
6	Banana	A5	0.5
7	Pear	A6	0.75
8	Orange	A7	1
9	Orange	A8	2
10	Orange	A9	2.5
11	Orange	A10	0.5
12	Orange	A11	8
13
14	Item	Max v Min variance %
15	Apple	0.5
16	Banana	0
17	Pear	0
18	Orange	15
Sheet1

Cell Formulas
Range	Formula
A15:B18	A15	=UNIQUE(CHOOSE({1,2},A2:A12,(MAXIFS(C2:C12,A2:A12,A2:A12)-MINIFS(C2:C12,A2:A12,A2:A12))/MINIFS(C2:C12,A2:A12,A2:A12)))
Dynamic array formulas.

 

[Eric W]

That looks correct. You just need to format the B15:B18 cells as Percentage.

 

[leebauman]

God...sorry, doing too much at once. I get it and it works great. Much appreciated!

 

[Eric W]

Happy to help! Thanks for the feedback.