Formula needed.

[Closed]
Report
Posts
4
Registration date
Tuesday April 23, 2013
Status
Member
Last seen
October 31, 2013
-
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
-
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 replies

Posts
2807
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 21, 2021
482
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
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
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.