Max and Min over multiple sheets with a condition

[bgbdwolf85]

Hello all,
I am having some trouble with my latest spreadsheet and havnt figured it out yet. Below is a breakdown of what is going on:

I have names in E2:E53 on sheet FY15, FY16, FY17.

I have a % in E:E53 on sheet FY15, FY16, FY17.

On each sheet, the same name may appear more than once in E2:E53.

On the "Totals" sheet, I have all the names listed in A2:A26.

On the "Totals" sheet, I have two columns; E for low value and F for the high.

I am looking for the lowest and highest value for each name from the FY15:FY17 sheets.

Any ideas, thanks for reading.

R/S
Jason

 

[svendiamond]

I think you typo'd... ?

I have names in E2:E53 on sheet FY15, FY16, FY17.

I have a % in E:E53 on sheet FY15, FY16, FY17.

 

[bgbdwolf85]

You are correct. It was way past my bed time when I decided to quit trying to figure it out and reach out for assistance. Below is all the information, corrected.

Hello all,
I am having some trouble with my latest spreadsheet and havnt figured it out yet. Below is a breakdown of what is going on:

I have names in E2:E53 on sheet FY15, FY16, FY17.

I have a % in G2:G53 on sheet FY15, FY16, FY17, this % corresponds to the name in the E column.

On each sheet, the same name may appear more than once in E2:E53.

On the "Totals" sheet, I have all the names listed in A2:A26.

On the "Totals" sheet, I have two columns; E for low value and F for the high.

I am looking for the lowest and highest value for each name from the FY15:FY17 sheets.

Sorry for the typo's, any ideas or guidance would be greatly appreciated, thanks for reading.

R/S
Jason

 

[svendiamond]

The easiest way would be to combine the data from all three sheets.

On a separate "helper sheet" just combine all the values into one big table. Then use a MAX(IF( and MIN(IF( array formulas. Like this:

Book1
	A	B
1	NAME	PERCENT
2	MATT	50
3	JOHN	30
4	FRANK	80
5	BILL	70
6	BILL	20
7	SUSAN	50
8	MATT	60
9	JOHN	35
10	FRANK	99
11	MATT	12
12	JOHN	88
13	FRANK	22
14	BILL	2
15	BILL	8
16	SUSAN	95
17	MATT	55
18	JOHN	60
19	FRANK	14
20	MATT	30
21	JOHN	30
22	FRANK	66
23	BILL	90
24	BILL	20
25	SUSAN	10
26	MATT	5
27	JOHN	1
28	FRANK	8
Helper

Book1
	A	B	C	D	E	F
1	NAMES	B	C	D	LOW	HIGH
2	MATT				5	60
3	JOHN				1	88
4	FRANK				8	99
5	BILL				2	90
6	SUSAN				10	95
Totals

Cell Formulas
Range	Formula
E2	{=MIN(IF(Helper!A:A=A2,Helper!B:B))}
F2	{=MAX(IF(Helper!A:A=A2,Helper!B:B))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bgbdwolf85]

svendiamond,
Thank you for your response. Its a very good idea and would work perfectly except this spreadsheet is living document where values are added weekly on the FY15:FY17 sheets, well not FY15 any longer. My goal is to track the final "Grade Point Average" (Column G) for each class an instructor (Column E) teaches;several a year. So when I look at my "Totals" page, I can see what the lowest class grade and highest class grade was for each instructor. Not sure if that helps clear things up or not. Thanks again.

R/S
Jason

 

[svendiamond]

So on your HELPER page just have formulas filled down to grab any new information from the FY17 sheet, etc.

Book1
	A	B
1	NAME	PERCENT
2	MATT	30
3	JOHN	30
4	FRANK	66
5	BILL	90
6	BILL	20
7	SUSAN	10
8	MATT	5
9	JOHN	1
10	FRANK	8
11	0	0
12	0	0
13	0	0
14	0	0
15	0	0
16	0	0
17	0	0
18	0	0
19	0	0
20	0	0
21	0	0
22	0	0
23	0	0
24	0	0
25	0	0
26	0	0
27	0	0
28	0	0
Helper

Cell Formulas
Range	Formula
A2	='FY17'!E2
B2	='FY17'!G2