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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
You could also try it without an array formula (without CTRL+SHIFT+ENTER)....

=SUMPRODUCT(MAX((D1:D100="female")*((J1:J100))))
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,520
Messages
6,142,941
Members
450,456
Latest member
Exidur

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
Back
Top