Multiple conditions in a IF formula

[claz2201]

Hi all,

I have a spreadsheet with multiple students (males and females) with some scores. I am wanting to create a 'top 5' sheet which shows top 5 males and top 5 females scores. I have found 2 problems with this.

1) Some scores need to be the lowest in the section (e.g. 100m sprint times), the document only sees the empty/ blank cells. Is there a formula which ignores blank cells.


2) I managed to get the correct scores for the highest categories (e.g long jump scores), but the 'index' formula I used didn't distinguish between males and females, just picked out the first matching score (which could be incorrect).

Any advice for either of these?

Claire

 

[SQUIDD]

Hi claire

Welcome to the board.

could you give an example of how your data is layed out and how and where you would like your results displayed.

many thanks

dave

 

[James006]

Hello,

My assumption is that your data is properly organised with headers ...

So why don't you try to Insert a Pivot Table ...

You will be able to carry out all sorts of analyses ...

Cheers

 

[claz2201]

So I have a sheet per year gruop (Year 7, Year 8 etc..)


Each sheet looks like this (with more events)..
A B C D
Name: Gender 100m Long Jump
Student A Male 15.5 3.71
Student B Male 17.2 4.52
Student C Female 15.5 3.25

I would like another sheet which shows the top 5 students in each event, based on their gender. So top 5 (smallest scores) males in 100m and top 5 females in 100.