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
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 - Oct 31, 2013 at 02:29 PM
Related:
- Formula needed.
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Credit summation formula - Guide
2 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Oct 31, 2013 at 12:31 PM
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
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
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.
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.
