Excel help needed

[NozSpark]

Hi guys,

I'm doing a spread sheet and need a little help (it's for my sailing club to do race results)

Say I have 6 cells (A1, A3, A5, A7, A8 & A9), they all have number in them that has been derived from a formula (which alters the actual race times into adjusted race times according to a boat handicap)------ this is repeated for several competitiors

What I need to do is total them up but with the ability to discard the lowest x number of results (x to be a number inputted into another cell)

Once those totals have been totted up I also need to be able to rank the results in order.

 

[NozSpark]

OK I have figured out the ranking part, but I still can't figure out the discard bit

 

[SPECIAL LOCATION]

Would you be able to do something using the 'IF' function..

If one cell greater or equal to the value in another cell then do a particular function...

You could have a second column that just copies the figures from your 'A's but only if they are greater than you ref value

otherwise set the duplicate column to cell to zero.

E.G.

If the number ref cell was C1

and B1, B3, B5, B7,B8 B9 were duplicates of A1.....A9 only if they exceed value in C1

B1 formula =IF(A1>=$C$1,A1,0)

B3 formula =IF(A3>=$C$1,A3,0)

B5 formula =IF(A5>=$C$1,A5,0)

B7 formula =IF(A7>=$C$1,A7,0)

B8 formula =IF(A8>=$C$1,A8,0)

B9 formula =IF(A9>=$C$1,A9,0)

That would give you zeros for each one less than C1 or the A column value if greater than C1

I am assuming setting a value to zero could be considered discarding?

 

[septiclecky]

Would you be able to do something using the 'IF' function..If one cell greater or equal to the value in another cell then do a particular function...

You could have a second column that just copies the figures from your 'A's but only if they are greater than you ref value

otherwise set the duplicate column to cell to zero.

E.G.

If the number ref cell was C1

and B1, B3, B5, B7,B8 B9 were duplicates of A1.....A9 only if they exceed value in C1

B1 formula =IF(A1>=$C$1,A1,0)

B3 formula =IF(A3>=$C$1,A3,0)

B5 formula =IF(A5>=$C$1,A5,0)

B7 formula =IF(A7>=$C$1,A7,0)

B8 formula =IF(A8>=$C$1,A8,0)

B9 formula =IF(A9>=$C$1,A9,0)

That would give you zeros for each one less than C1 or the A column value if greater than C1

I am assuming setting a value to zero could be considered discarding?

Depends upon what the version of excel he is using as there is a limit of 7 IF functions per formula on 2003 and earlier versions don't know if this is increased in 2010 version as still trying to find my way around it with them changing it from the outlay in 2003 and earlier versions.

 

[NozSpark]

Not really using Excel TBH ,,,, numbers on my iPad:O but most of the expressions are the same anyhow

Anyway I've managed to sort it with the help of an Apple support page