Excel Experts read on ;O)

qwerty1982

Hey guys.

Im trying to sort out a league table containg 173 odd people,
basically i have put in the data for 3 out of 4 rounds of a series.
I have found the formula to tell me what position every one should be in but is there a way i can get them into actuall order, ie first second third etc etc
Am i making sense?
imagine this.... down the left hand side it has there postions based on points... so could go 1, 14, 4, 27, 88,
How can i get the number postions into the ACTUAL correct positions, 1, 2 ,3 ETC ETC but keep all the data in line...

ugh, really struggling to make this make sense!
Maybe an image will help...



As you can see, the top 3 riders are in correct order, but then jason should be in position 18 but is in 4th. how can i get them to be arranged in correct order but also take there round points with them??
Any ideas any one?
Im sure it has some thing to do with the IF formule....

The Northern Monkey

EDIT: select all of the data under the "round 1, round 2 etc" columns and add a filter.

on the column you want to sort by, click the drop down arrow and sort by number lowest to highest.

It should then keep all the data in the right columns.

BTW...can't see your image very well so i'm kinda guessing!

redvee

Not sure how to do this with a formula but the way to do it manually would be select the columns with the data in, name and points, then select sort by column XX with XX being the one with the points value.

The Northern Monkey

all you need to do is add a filter and sort the column numerically.

no formulas needed.

The Northern Monkey

http://www.fgcu.edu/support/office2007/ ... Filter.asp

qwerty1982

hmm
thanks for the quick replies... but i dont get it lol

If you right cliock the image, then copy the URL properties and open a new window, add the url it should bring the pic up bigger...

I need to sort the whole table, inc all the rounds, im not sure what to add as the filter~?

qwerty1982

Solved!!!
Used the sort bit after highlighting it all
thank you guys, knew you lot would help me!!!

qwerty1982

ok got another Q!

The series is 4 rounds long, I only want the top 3 rounds to count. Is there a way I can make the total points only add up the top 3 rounds for each rider?

qwerty1982

Ok im almost there...

If the values were running enxt to each other i could use this formule
=SUM(LARGE(A1:F1,{1,2,3}))

How ever i do not have mine set out like that. The values i want would be for argumeents sake A1, C1, E1, G1
Have tried replacing the A1:G1 with A1+C1+E1+G1 but i get an error.

Any help~?

The Northern Monkey

=sum(a1+c1+e1+g1)


you can add cells with nothing in them, then when you do populate the cell the sum will automaticall alter to show the new value (not sure if you want that though).

The Northern Monkey

actually you can just use

=a1+c1+e1+g1

qwerty1982

Yeah i know that woul add the values up, thats not what i am after.

will try and explain better.

If cells A1 has 122 C1 has 134 E1 has 110 F1 125

Cells B1, D1, F1 all contain values as well, the round numbers.

I want to the formule that works out the 3 highest values and adds them together.

This is the formulee =SUM(LARGE(A1:F1,{1,2,3})) but as you can see it uses A1:F1 which will add ALL values between A and F where i only want the values with the points in it.

Is this making sense?

I have tried =SUM(LARGE(A1+C1+E1=F1,{1,2,3})) but it does not work.

The Northern Monkey

=SUM(LARGE(B1:F1,{1,2,3})) will only add together the 3 highest values in a range, it doesn't add them all!

qwerty1982

YES I KNOW THAT, BUT HOW MY TABLE IS LAID OUT I HAVE THE ROUND NUMBER THEN THE POINTS, THEN THE ROUNDD NUMBER THEN THE POINTS AND SO ON.... SO AS THE ROUND NUMBER IS A VALUE IT INCLUES THATAS WELL, I DONT WANT IT TO INCLUDE THAT.... OOPS CAPS LOCK!
Does that make sense?

The Northern Monkey

NO BECAUSE I CAN'T SEE YOUR FECKIN PHOTO... I SAID IT WAS TOO SMALL IN MY FIRST POST.

FFS.

qwerty1982

lol sorry mate, i wasn't shouting, just had caps on for some reason and was being lazy.
Any way i can make the pic bigger? thats what Photo bucket has done?
Sorry for being a pain in arse dude... just struggling with getting accross what i want.

MrChrisP

Yeah the image is too small mate. I take it the rows just hold the data for each individual rider? If that's the case just take an image of the top 5 or 6 rows and post that up, which should give a bigger picture and show all what it is you mean.

Am I right in understanding that if for example Rider 1 gets the following points:

Round1: 10
Round2: 5
Round3: 30
Round4: 25

Then you want a formula to only add up the totals for rounds 1,3&4?

If that's the case then it should be pretty simple. Just change the round numbers to words so Round one, Round Two etc.

That way the formula that you and Northern Monkey have already suggested should give you the desired results....

Stu 74

Create 4 new columns next to each other (which you can then hide).

Column 1 = Round 1 Score + Round 2 Score + Round 3 Score
Column 2 = Round 1 Score + Round 2 Score + Round 4 Score
Column 3 = Round 1 Score + Round 3 Score + Round 4 Score
Column 4 = Round 2 Score + Round 3 Score + Round 4 Score

Then use =MAX(Column 1:Column 4) to get the highest 3 round combination.

Stu

John Stevenson

Or you can glom it all together in a mess of nested IFs:

=A2+C2+E2+G2-IF(IF(IF(G2<E2,G2,E2)<C2,IF(G2<E2,G2,E2),C2)<A2,IF(IF(G2<E2,G2,E2)<C2,IF(G2<E2,G2,E2),C2),A2)

This seems to give consistent results regardless of the position of the lowest number, but this is the only test I have done, so use with caution:

http://dl.dropbox.com/u/156728/Book1.xls