return value depending on multiple columns

[XFG]

Hi all,

I have a sheet where i need to return a value depending on multiple columns. I want to do this to calculate the efficiency of an installation part. The efficiency of the installation part is calculated per batch. I don't want to average all the batch efficiencies. Attached a photo of the excel table. For simplicity i have three installation parts and all three have an IN and OUT. Every installation part has a different owner. Every owner fills in the table when the analyses are reported, so the table will not be filled in chronological order.

So, for the efficiency of the washer OUT (batch 1) it needs to find the relating washer IN value so i can calculate the efficiency of batch 1 for the washer.
The formula for calculating the efficiency of the washer per batch is as follows:
1 - (washer OUT/washer IN).

Does anyone know how to setup this formula? Do i need helper columns?

If it is not clear yet, please let me know.


Kind regards,
XFG

 

[Toadstool]

Hi XFG,

Does this work for you?

XFG.xlsx
	A	B	C	D	E	F	G	H	I
1	Install	Client	Y	W	D	Batch	Antimony	Efic	Result
2	Crusher IN					3	0.33
3	Crusher OUT					3	0.34		-3%
4	Left leg IN
5	Left leg OUT
6	Washer IN	RTP	2017	47	1	1	0.29
7	Washer IN	RTP	2017	47	1	2	0.31
8	Washer OUT	RTP	2017	47	1	1	0.31		-7%
9	Seef IN
10	Seef OUT
11	Washer OUT	RTP	2017	47	1	2	0.33		-6%
Sheet1

Cell Formulas
Range	Formula
I2:I11	I2	=IF(RIGHT(A2,4)<>" OUT","",IFERROR(1-(G2/INDEX($G$2:$G$9999,MATCH(1,INDEX(($A$2:$A$9999=LEFT(A2,SEARCH(" OUT",A2))&"IN")*($F$2:$F$9999=F2),0),0))),""))

 

[XFG]

Hi Toadstool,

Thank you for the quick reply. This works for me.

However, i see now, i was not clear enough. Besides batch, the returned value should also depend on the client, year, week and day column.
For Crusher IN and Crusher OUT to calculate the efficiency it should only apply the formula below if the client, year, week, day and batch are the same.
formula: 1 - (Crusher OUT/Crusher IN).

Is that possible?

 

[Toadstool]

Here is the amended calculation.

Crusher is calculated as client, year, week, day and batch are the same for IN and OUT.
Left leg is not calculated as they're different days.

XFG.xlsx
	A	B	C	D	E	F	G	H	I
1	Install	Client	Y	W	D	Batch	Antimony	Efic	Result
2	Crusher IN	ABC	2017	44	1	3	0.33
3	Crusher OUT	ABC	2017	44	1	3	0.34		-3%
4	Left leg IN	LLX	2017	46	2	1	0.55
5	Left leg OUT	LLX	2017	46	3	1	0.44
6	Washer IN	RTP	2017	47	1	1	0.29
7	Washer IN	RTP	2017	47	1	2	0.31
8	Washer OUT	RTP	2017	47	1	1	0.31		-7%
9	Seef IN
10	Seef OUT
11	Washer OUT	RTP	2017	47	1	2	0.33		-6%
Sheet1 (2)

Cell Formulas
Range	Formula
I2:I11	I2	=IF(RIGHT(A2,4)<>" OUT","",IFERROR(1-(G2/INDEX($G$2:$G$9999,MATCH(1,INDEX(($A$2:$A$9999=LEFT(A2,SEARCH(" OUT",A2))&"IN")*($F$2:$F$9999=F2)*($B$2:$B$9999=B2)*($C$2:$C$9999=C2)*($D$2:$D$9999=D2)*($E$2:$E$9999=E2),0),0))),""))

 

[XFG]

Hi Toadstool,

This is exactly what i want. Thank you very much for the quick solution.

KR, XFG

 

[Toadstool]

You're welcome!
Thanks for the feedback.