help!

rndgirl

New Member
Joined
Sep 24, 2006
Messages
4
HI, i am tryin to make a spreadsheet for my sports club. I know how to find the highest scoring player but would like a formula that would tell me who the highest scoring male/female/coach was and what they scored. could anyone provide me with a formula??

thanks in advance
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
If you have the category, male/female etc. in column A and score in column B then to find the highest scoring female player

=MAX(IF(A1:A100="female",B1:B100))

confirmed with CTRL+SHIFT+ENTER
 

rndgirl

New Member
Joined
Sep 24, 2006
Messages
4
this hasn't worked, the male/female category is in column D and their scores are in column J, i hvae tried changing it but it still doesnt work.

rndgirl
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
What result do you get?

You need to confirm with CTRL+SHIFT+ENTER. Select cell with formula, press F2 then hold down CTRL and SHIFT buttons and press ENTER. Curly braces like { should appear around the formula in the formula bar.
 

rndgirl

New Member
Joined
Sep 24, 2006
Messages
4

ADVERTISEMENT

yep that happens, but it says microsoft excel cannot calculate formula.

this is what i ahve changed the formula to

=MAX(IF(D1:D100="female",J1:J100))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Not sure why that doesn't work - it works for me - does yours look like this?
Book1
CDEFGHIJK
1female0
2male1
3female82
4male3
5female4
6male5
7female6
8male7
9female8
10male9
11
Sheet3


formula in G3

=MAX(IF(D1:D100="female",J1:J100))

confirmed with CTRL+SHIFT+ENTER
 

Doug.T

Board Regular
Joined
Sep 19, 2006
Messages
80

ADVERTISEMENT

You could also try it without an array formula (without CTRL+SHIFT+ENTER)....

=SUMPRODUCT(MAX((D1:D100="female")*((J1:J100))))
 

rndgirl

New Member
Joined
Sep 24, 2006
Messages
4
Nope that doesnt work either

Here is a rough copy of my spreadsheet, i need formulas for the cells highlighted in yellow. I don't mind if it shows just the score but would like the name to come up too.

SSheet-1.jpg
[/img]
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
rndgirl

For the data posted, I believe that you need Barry's formula adjusted as follows. This formula in cell I26 for Best Female:
=MAX(IF(D1:D20="Female",I1:I20))
confirmed with Ctrl+Shift+Enter

In I23 for Best Team:
=MAX(IF(H1:H20="Team Total",I1:I20))
confirmed with Ctrl+Shift+Enter
etc
 

Forum statistics

Threads
1,136,347
Messages
5,675,235
Members
419,555
Latest member
Paddington

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top