Hello,
I'd like to find a student's highest test score from a table which shows all the results every time a particular test has been taken. One student, for example, has taken the test four times this year and scored 450, 465, 455, and 460. Another student has taken the test twice and scored 470 and 480. What formula should I use to return 465 and 480 for these two students? Can I use MATCH and LARGE somehow? Thanks!
Please see if below formula works. Name of the students are in the column B and Marks are in column C.
{=MAX(IF($B$3:$B$11=F3,$C$3:$C$11))}
Thanks for the reply. That's not working, unfortunately. What's the F3 doing in your formula? Thanks!
F3 should have the name of the student for whom you want to get the maximum marks. sorry forgot to mention this in the last post. Also find attached the file containing formula for your reference.
name of students in B3 to B11, C3 to C11 are marks of studentname of student whose mark you are searching is in D3 then
in E3 copy paste below
=MAX(IF($B$3:$B$11=D3,$C$3:$C$11)) then hold control and shift together and then hit enter to make it array formula
@excelfundoo u forgot to mention array confirmation of formula
Or you could use
copy paste below in E3 and simple enter
=MAX(INDEX(($B$3:$B$11=$D$3)*($C$3:$C$11),))
Change the cell references according to your data set
Last edited by hemesh; 01-11-2016 at 05:06 AM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
OK, thanks, ExcelFandoo, I get it. Though the way I need it to work is a bit different. I've amended your file to show you what I mean: I need to pick up the highest scores from one table and put them into another one. Thanks ever so much for your help.
MaxIf.xlsx
I think in your data B and Month June should be November instead of June
try below in Column M
=MAX(INDEX((Table1[Name]=[@Name])*(Table1[Marks]),))&"("&INDEX(Table1[Date],MATCH(J2&MAX((Table1[Name]=J2)*(Table1[Marks])),Table1[Name]&Table1[Marks],0))&")"
Copy paste above in M2 then hold control and shift together and then hit enter
Or
=MAX(INDEX((Table1[Name]=[@Name])*(Table1[Marks]),))&"("&INDEX(Table1[Date],SUMPRODUCT(((Table1[Name]=J2)*(Table1[Marks])=MAX((Table1[Name]=J2)*(Table1[Marks])))*(ROW(Table1[Name])-1)))&")"
copy paste above and hit enter
Last edited by hemesh; 01-11-2016 at 05:33 AM.
Hello, yes, you're right about data B: Thanks!
My version of excel (2007) won't accept your array formula with [@Name]). Thanks for the help.
Or Try
M2=MAX(IF($A$2:$A$16=J2,$B$2:$B$16))&"("&INDEX(Table1[Date],MATCH(J2&MAX(IF($A$2:$A$16=J2,$B$2:$B$16)),Table1[Name]&Table1[Marks],0))&")" With CRTL+SHIFT+ENTER
Formula:
Please Login or Register to view this content.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
you data is in table format what you have uploaded here and those are table syntax are you using table formatting in your actual sheet
see attached file
Last edited by hemesh; 01-11-2016 at 05:47 AM. Reason: attachment
Thanks, Ankur—your formula works brilliantly. I'm going to adapt the formula to use in my actual sheet. Thanks also to everyone who helped.
Glad to help you and thanks for feedback. Please mark it as solved if you satisfy and please add reputation by clicking * star icon left corner of your screen to those post which you helpout.
@ hokkaido could you atleast go through the file I attached. Anyways
how about below just copy paste in m2 and hit enter
Formula:Please Login or Register to view this content.
change references according to your data
The formula works apart from the fact that it is returning numbers and not dates. You can see this in the Excel file here: MaxIf.xlsx
Thanks!
try below then
=MAX(IF($A$2:$A$16=J2,$B$2:$B$16))&" ("&TEXT(INDEX(Table1[Date],MATCH(J2&MAX(IF($A$2:$A$16=J2,$B$2:$B$16)),Table1[Name]&Table1[Marks],0)),"MMM")& ")"
Thanks, again. But it's not working in my actual table as I have my dates formatted as, for example, 9 May 2015.
How are we to know your date format if you don't show it in posted sample files ?????
your date is actual date or is it formatted as text.
OK, apologies. The date format in this sample file is now the same as the format (formatted as date) in my actual file: MaxIf.xlsx
Is it better to format dates as text? Thanks!
@hokkaido ! Did you try formula from post 17
it is need to be array entered
copy paste formula and then hold control and shift together and then hit enter key to make it array formula
Don't you sleep, hemesh? I've just tried it and it works: thanks! Later today, I'll adapt it for my actual worksheet and use "D MMM YYYY".
You are welcome !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks