help!

[rndgirl]

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

 

[barry houdini]

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]

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]

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]

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]

Not sure why that doesn't work - it works for me - does yours look like this?

Book1
	C	D	E	F	G	H	I	J	K
1		female						0
2		male						1
3		female			8			2
4		male						3
5		female						4
6		male						5
7		female						6
8		male						7
9		female						8
10		male						9
11
Sheet3

formula in G3

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

confirmed with CTRL+SHIFT+ENTER

 

[Doug.T]

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

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

 

[rndgirl]

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.

[/img]

 

[Peter_SSs]

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