| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 10859 | 2001-08-17 09:45:00 | Excel Functions | Guest (0) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 17131 | 2001-08-17 09:45:00 | I have a column of 20 names, next to which is a column of exam results. I can use the MAX function at the bottom of the column to find the highest mark. How do I put the text name next to this mark?? | Guest (0) | ||
| 17132 | 2001-08-18 02:36:00 | Presuming the marks are not sorted, you could try this: Lets say your student names are in column b and the mark in column c. The first mark starts in row 2 and the max is shown in rown 22 In a spare column (say column d, for row 2: =if(c2=$c$22,b2,'') This looks at each student. If their mark equals the maximum, their name is shown, if not: nothing is shown. Copy this down all rows Format the cells as hidden Where you want the name to appear, type: =Concatenate(d2,d2,d3,d4,d5,d6,d7,d8,d9....,d21) This formula 'adds' all the text together (where the mark doesn't match the maximum we're not showing the name) so hey presto - whoever's name matches the maximum is displayed. That's the only way I can see it working. All lookups presume that you have pre-sorted the values - when I believe you've probably got the layout in student name order? Good luck. |
Guest (0) | ||
| 17133 | 2001-08-18 13:04:00 | If for example the names are in Column B and the marks in Column C the following formula when placed in any other cell will display the highest mark and the name of the person together in the same cell. =MAX(C1:C20)&' '&INDEX(B1:C20,MATCH(MAX(C1:C20),C1:C20,0),1) HTH |
Guest (0) | ||
| 17134 | 2001-08-19 23:13:00 | Thanks guy's, I will try both possible solutions. If negative I'll post problem again. | Guest (0) | ||
| 17135 | 2001-08-19 23:18:00 | Thanks, I'll try your suggestions. If negative I'll post my problem again. | Guest (0) | ||
| 17136 | 2001-08-20 01:18:00 | Hi Guys, Brent O: There appears to be an error in your formula ' =if(c2=$c$22,b2,'') '. This should read =if(c2=max($c$2:$c$22),b2,''), then its OK. Russell D: your suggestion works OK Thanks guys, both suggestions now give me an alternative results. Regards |
Guest (0) | ||
| 1 | |||||