I suppose, that Pivot Table would solve this problem. But how solve this
problem not using Pivot Table?

--
A.B.


"CLR" rakstîja:

You might try using a Pivot Table to get the results you're after. Give it a
a try, and if you need help, post back.

Vaya con Dios,
Chuck, CABGx3




"Aivis" wrote:

Hi all!

My problem is current:
I want to find best student grade from a list (see below) and worst student
grade from the list, but I can' t do it if student is more than 2 times in
this list.
In column E, I use following formula:
=OFFSET($I$1;MATCH(MAX(C2;D2-C2);$J$2:$J$7;0);0), but in column F:
=OFFSET($I$1;MATCH(IF(MIN(C2;D2-C2)=0;C2;MIN(C2;D2-C2));$J$2:$J$7;0);0).
These formula is OK when student have one or two records in the list. But
how can I get right answer if student have more than 2 records?

A B C D E F G H
1 Name Grade1 Grade2 SUMIF Worst1 Best1 Worst2 Best2
2 Joe B 2 6 D B C A
3 Alice B 2 9 #N/A B D B
4 John C 3 3 C C C C
5 Alice C 3 9 F C D B
6 Josef A 1 1 A A A A
7 Philip F 6 6 F F F F
8 Karin D 4 6 D B D B
9 Karin B 2 6 D B D B
10 Joe C 3 6 C C C A
11 David C 3 5 C B C B
12 Joe A 1 6 E A C A
13 David B 2 5 C B C B
14 Alice D 4 9 E D D B

I J
1
2 A 1
3 B 2
4 C 3
5 D 4
6 E 5
7 F 6

*Column C is students' grade in number format (used VLOOKUP formula).
**Column E and F shows students' worst/best grade calculated by formula;
***Column G and H shows students' worst/best grade (how it should be;
inserted manually).

--
A.B.