Need to find the max value from a table

[graeme27uk]

Hello all,

I hope the attached link works and makes sense.

I have some data that I need to find the max value for from a table. The headings for the table are not necessarily constant as they can be repeated several times. However, in the final table I want to be able to draw down the max result for that particular value/aspect.

http://s250.photobucket.com/user/gra...excel.gif.html

Any solutions are welcome.


Cheers,
Graeme

[FDibbins]

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

[graeme27uk]

Ok, trying again.

The uploader doesn't like me and I have tried several times.

What I am after is to be able to select the maximum value from a table but that has repeated column headings.

b1f 2012 b1f 2011 c1f 2012 p1f 2013 b1f 2013 c1f 2011 p1f 2014 b1f 2014 b1h 2011 c1h 2014 p1h 2013 b1h 2013 c1h 2011 p1h 2010
A 10 20 30 40 50 20 51 25 10 21 15 61 12 12
B 12 22 32 44 55 25 15 21 15 15 19 24 25 23
C 14 24 34 48 60 10 21 15 13 35 35 63 34 31
D 16 26 36 52 65 15 12 16 23 28 94 59 3 2
E 18 28 38 56 70 51 58 17 29 34 34 36 37 39


The first column is just names (text). The column headings are exam papers taken (for example) but each with a specific year attached. People can take a certain unit (say B1F) any number of times as long as its a different year paper.

What I then need to do is consolidate this all to give me the best marks for say, B1F out of all the ones that they have completed for B1F out of the various years.

So that I end up with a table like....

B1F B1H C1F C1H P1F P1H
A
B
C
D
E

With just the maximum mark for each unit for each person.

Hope that helps and makes sense.

[FlameRetired]

Try this as in the attached. It's an array-entered formula.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

The results look like this.

Row\Col	A	B	C	D	E	F	G	H
11		b1f	b1h	c1f	c1h	p1f	p1h
12	A	50	61	30	21	51	15	In B12:G16 {=MAX(INDEX($B$2:$O$6,MATCH($A12,$A$2:$A$6,0),N(IF(1,IF(B$11=LEFT($B$1:$O$1,3),COLUMN($B$1:$O$1)-MIN(COLUMN($B$1:$O$1))+1)))))}
13	B	55	24	32	25	44	23
14	C	60	63	34	35	48	35
15	D	65	59	36	28	52	94
16	E	70	36	51	37	58	39

Do these returns look like what you expect?

[oeldere]

I would restore the data in a (verticaly) table. (e.g. with a macro).

After that a pivot table to determine the max.

Since you don't add an small excel file without confidential information, I can't show you.

If you add an excel file, please also add the expected result in your file.

[graeme27uk]

Thank you for the responses so far. I have tried to attach the file but again, that feature doesn't seem to want to work. The exemplar file I have for demonstration is 1862k large, so maybe too big to attach?

The real file is much larger!

I could email it to someone to look at I guess?

However, I think the array works, but now I am not sure how to apply it to the actual file I want... one of those cases where its hard to explain without having the file in front of you.

I'd be happy to discuss the file over Skype or such if that is any help.

Cheers again,
Graeme

[Glenn Kennedy]

The file size limit is 1 Mb. So, cut it down a bit more... or ZIP it. the limit for zipped files is considerably larger.

[graeme27uk]

Tried zipping it but whatever I try to attach, it just spins round and around not really getting anywhere; even if I leave it a good 10 - 15 mins to attach.

I can't really remove anything as I have already scaled down the spreadsheet.

[graeme27uk]

SCIENCE TRACKER exemplar.zip

Hope this works.

So...


Cell F2 is a drop down menu of the different exams.

Each exam has different grade boundaries and UMS and such (just the way it works).

As you can see, this is repeated several times so that students can sit various exams, either different or the same units, or even the same year and paper. This gives maximum flexibility for data entry.

I then need to return the maximum UMS values for each unit (B1F, B1H, C1F, etc)- for example cell N7

[AliGW]

Do you want the best raw mark or UMS mark? I presume UMS.

EDIT: Sorry - can't read ... DOH!

[FlameRetired]

I then need to return the maximum UMS values for each unit (B1F, B1H, C1F, etc)- for example cell N7

I am not finding any cell N7 with data in it. To which sheet(s) do you refer, and where do you want the output? I would assume in the Sheet3 table?

Edit Please ignore the above. I need to find some new questions.

[graeme27uk]

Would using an MAX INDEX MATCH work?
.
If the issue is partly that there is no reference to B1F or such then I can modify that relatively simply. I have not used INDEX MATCH before so I would be going into new ground.

[FlameRetired]

Similar. This is a much simpler array formula than my previous.

Please Login or Register  to view this content.