Formula needed.

Closed
ahamaz Posts 4 Registration date Tuesday April 23, 2013 Status Member Last seen October 31, 2013 - Oct 31, 2013 at 08:20 AM
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 - Oct 31, 2013 at 02:29 PM
Hi dear
i am looking for an formula that can resolve the following issue.
I have a table of:

Actual....................................................Output
Names:..............Marks....................Names:......Attempts......Highest Marks
Richard..............0.02476..................Elina...............1.............0.00234
Tommy..............4.87457..................Jack................2............0.03764
Elina..................0.00234..................Michael...........2.............6.87461
Richard..............0.12647..................Richard............4............1.07463
Elina..................0.01324..................Tommy............1............4.87457
jack...................0.03764
Richard..............1.07463
Michael...............6.87461
Jack....................0.00456
Tommy...............0.64789
Richard...............0.12894
Michael..............3.87645


I need output list from actual list:
- Formula shout check the;
1- Same names from the name list like Richard.
2- Count the numbers like 4.
3- Get the heighest marks out of 4 attempts.
4- and display like OUTPUT in alphabetically order name wise.

I will appreciate your efforts, if y look into it.
looking forward to hear from you.
regards
ahamaz





2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 31, 2013 at 12:31 PM
Hi Ahamaz,

To get the number of attempts per name:
=SUM(IF($A$3:$A$14=D3,1,0))
This is an array formula and needs to be confirmed by hitting Ctrl+Shift+Enter.
$A$3:$A$14 is the range of all the names.
D3 is the location of the name you want to look up.

To get the highest mark per name:
=MAX(IF($A$3:$A$14=D3,1,0)*$B$3:$B$14)
This is also anarray formula, so confirm by hitting Ctrl+Shift+Enter.

To get the list of names you can copy the original list to a new column and remove the duplicates. The way to do this differs per Excel version.

Best regards,
Trowa
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Oct 31, 2013 at 02:29 PM
Hi

I am asumming that your Actual is in A1:B13 including the column headers and the output is in D1:F6 including headers.

Then in the following cells and copy down.

D2: =INDEX($A$2:$A$13,MATCH(0,COUNTIF($A$2:$A$13,"<"&$A$2:$A$13)-SUM(COUNTIF($A$2:$A$13,"="&D$1:D1)),0)). This is an array formula, Ctrl + Shift + Enter to commit.

E2: =COUNTIF($A$2:$A$13,$D2)

For Excel 2010>
F2: =AGGREGATE(14,6,$B$2:$B$13/($A$2:$A$13=$D2),1)

If you have Excel 2007<
F2: =MAX(IF($A$2:$A$13=$D2,$B$2:$B$13)) This is also an array formula, Ctrl + Shift + Enter to commit.