Equation to find % difference change of data within the same column

[Liliquestionsxlxs]

I need an equation that would find the percent difference of the Ratio column that uses the step name and batch ID as the differentiators.

For example:
% Difference = ((Baseline 2 of Batch 23 - Baseline 1 of Batch 23)/Baseline 1 of Batch 23) =0.245109893
% Difference = ((Ratio 2 of Batch 23 - Ratio 1 of Batch 23)/Ratio 1 of Batch 23) = 0.196243382
% Difference = ((Ratio 2 V/A of Batch 23 - Ratio 1 V/A of Batch 23)/Ratio 1 V/A of Batch 23) = -0.238607548

I would like to have Excel be able to differentiate between each batch ID and using the same batch ID and corresponding step names before doing the calculation. I keep on doing this by hand and would like to not do that anymore.

I want to avoid having to separate all the data into two or more columns to calculate the % difference so no I do not want to create a separate. I would like to be able to have Excel do that calculation automatically and be able to use that same equation on thousands of rows.

I tried this equation but it's adding the data of all the columns so it's not doing what I want it to do.

=IF(LEFT(B3,7)<>"Ratio","",IF(MID(B3,7,1)="1",SUMIFS(C:C,A:A,A3,B:B,B3)-SUMIFS(C:C,A:A,A3,B:B,"Ratio 2"&RIGHT(B3,LEN(B3)-7)),SUMIFS(C:C,A:A,A3,B:B,B3)-SUMIFS(C:C,A:A,A3,B:B,"Ratio 1"&RIGHT(B3,LEN(B3)-7))))

 

[StephenCrump]

Welcome to the Forum!

Here's one way that might work for you:

	A	B	C	D
1	Batch	Step	Ratio	% Diff
2	23	Baseline 1	19	-32%
3	23	Baseline 2	13
4	23	Ratio 1	18	-28%
5	23	Ratio 1 V/A	15	+7%
6	23	Ratio 2	13
7	23	Ratio 2 V/A	16
8	52	Ratio 2	18
9	52	Ratio 2 V/A	13
10	52	Baseline 1	12	+58%
11	52	Baseline 2	19
12	52	Ratio 1	10	+80%
13	52	Ratio 1 V/A	17	-24%
14	86	Ratio 1	17	-41%
15	86	Ratio 1 V/A	17	+6%
16	86	Ratio 2	10
17	86	Ratio 2 V/A	18
18	86	Baseline 1	15	-20%
19	86	Baseline 2	12
Sheet1

Cell Formulas
Range	Formula
D2:D19	D2	=IF(ISERR(SEARCH(" 1",B2)),"",INDEX(C$2:C$19,MATCH(1,(A$2:A$19=A2)*(B$2:B$19=SUBSTITUTE(B2,"1","2")),))/C2-1)

 

[Liliquestionsxlxs]

IT WORKS! Thank you very much!

 

[Liliquestionsxlxs]

IT WORKS! Thank you very much!

Okay nevermind, it worked on my sample data sheet, but on my sheet I have thousands of entries with different batch numbers and it works for the very first reading but not the following readings

 

[StephenCrump]

... it works for the very first reading but not the following readings

You should just need to adjust the final row number in the formula:

	A	B	C	D
1	Batch	Step	Ratio	% Diff
2	23	Baseline 1	19	-32%
3	23	Baseline 2	13
4	23	Ratio 1	18	-28%
5	23	Ratio 1 V/A	15	+7%
6	23	Ratio 2	13
7	23	Ratio 2 V/A	16
8	52	Ratio 2	18
9	52	Ratio 2 V/A	13
10	52	Baseline 1	12	+58%
11	52	Baseline 2	19
12	52	Ratio 1	10	+80%
13	52	Ratio 1 V/A	17	-24%
14	86	Ratio 1	17	-41%
15	86	Ratio 1 V/A	17	+6%
16	86	Ratio 2	10
17	86	Ratio 2 V/A	18
18	86	Baseline 1	15	-20%
19	86	Baseline 2	12
20	1	Blah 1	12	+67%
21	1	Blah 2	20
22	1	More blah 1 blah	11	+64%
23	1	Something 1 else	15	+27%
24	1	More blah 2 blah	18
25	4	Something 2 else	18
26	2	Blah 1	17	+6%
27	2	Blah 2	18
28	2	More blah 1 blah	18	-
29	2	Something 1 else	13	-23%
30	2	More blah 2 blah	18
31	2	Something 2 else	10
32	3	Blah 1	18	-17%
33	3	Blah 2	15
34	3	More blah 1 blah	20	-50%
35	3	Something 1 else	12	+17%
36	3	More blah 2 blah	10
37	3	Something 2 else	14
38	4	Blah 1	20	-
39	4	Blah 2	20
40	4	More blah 1 blah	19	-16%
41	4	Something 1 else	10	+80%
42	4	More blah 2 blah	16
43	1	Something 2 else	19
Sheet3

Cell Formulas
Range	Formula
D2:D43	D2	=IF(ISERR(SEARCH(" 1",B2)),"",INDEX(C$2:C$43,MATCH(1,(A$2:A$43=A2)*(B$2:B$43=SUBSTITUTE(B2,"1","2")),))/C2-1)

 

[Liliquestionsxlxs]

Fortunately the first formula works and it is the column at the very end in bold. It looks like the first formula only considers one filter which would be the batch number but I just noticed that in my larger file I also filtering the data using a section number as in the picture. I guess the question here is can this formula be edited to have a second filter? Something along the lines of adding that?

=IF(ISERR(SEARCH(" 1",B2)),"",INDEX(C$2:C$19,MATCH(1,(A$2:A$19=A2, B$2:B$19=B2)*(B$2:B$19=SUBSTITUTE(B2,"1","2")),))/C2-1)

 

[StephenCrump]

I guess the question here is can this formula be edited to have a second filter? Something along the lines of adding that?
=IF(ISERR(SEARCH(" 1",B2)),"",INDEX(C$2:C$19,MATCH(1,(A$2:A$19=A2, B$2:B$19=B2)*(B$2:B$19=SUBSTITUTE(B2,"1","2")),))/C2-1)

Close! Try:

	A	B	C	D	E
1	Batch	Section	Step	Ratio	% Diff
2	23	1	Baseline 1	19	-32%
3	23	1	Baseline 2	13
4	23	1	Ratio 1	18	-28%
5	23	1	Ratio 1 V/A	15	+7%
6	23	1	Ratio 2	13
7	23	1	Ratio 2 V/A	16
8	23	2	Baseline 1	12	+8%
9	23	2	Baseline 2	13
10	23	2	Ratio 1	14	+21%
11	23	2	Ratio 1 V/A	10	+50%
12	23	2	Ratio 2	17
13	23	2	Ratio 2 V/A	15
14	52	3	Ratio 2	18
15	52	3	Ratio 2 V/A	13
16	52	3	Baseline 1	12	+58%
17	52	3	Baseline 2	19
18	52	3	Ratio 1	10	+80%
19	52	3	Ratio 1 V/A	17	-24%
20	52	4	Ratio 1	17	-41%
21	52	4	Ratio 1 V/A	17	+6%
22	52	4	Ratio 2	10
23	52	4	Ratio 2 V/A	18
24	52	4	Baseline 1	15	-20%
25	52	4	Baseline 2	12
Sheet1

Cell Formulas
Range	Formula
E2:E25	E2	=IF(ISERR(SEARCH(" 1",C2)),"",INDEX(D$2:D$25,MATCH(1,(A$2:A$25=A2)*(B$2:B$25=B2)*(C$2:C$25=SUBSTITUTE(C2,"1","2")),))/D2-1)

 

[Liliquestionsxlxs]

IT WORKS!!!! Thank you very much!!! ???