I am trying to create a spreadsheet for race results, and am having a hard time working out how to assign points to a position number.

For example C3 contains finishing position and I want G3 to add the points equivalent.

I presume I need to add 1st=14, 2nd=12 etc somewere and then get cell G3 to look at it and add the value.

Can anyone explain how to do this to a layman please ?

A Vlookup table would do it. Have a look at this and scroll down a bit. They done similar but with school grades. Put the VLookup table on another sheet and go from there

http://www.contextures.com/xlfunctions02.html

Cheers Martyn, got it working, but having to manually edit the columns for each race now, as pasting the formula moves the cell references... fine for most of it, but not so good when it moves the the lookup !

Oh well, it'll be a long job it seems

Cheers Martyn, got it working, but having to manually edit the columns for each race now, as pasting the formula moves the cell references... fine for most of it, but not so good when it moves the the lookup !

Oh well, it'll be a long job it seems

you can put a $ in front of the column/row and it won't change it when you copy/paste. the cell references are assumed to be relative if you copy/paste, but if you put =$b$1, in a cell and copy/paste it will always refence b1 (you can also use =$b1 or =b$1 and have only the row or column kept the same)

Edited 8 May, 201214 yr by sdenny

That didn't work for me Sdenny, probably because I have other cells in the equation.

My formula looks like this : =VLOOKUP(O3,S1:T17,2,FALSE) +P3 +Q3

I don't know what the "2,false" at the end does, but it works so I left it in

What I am aiming to achieve is it looks in O3 to see the race position, and puts the points in the box the formula is in (in this case R3). Then it adds any entry in P3 and Q3 (points for fastest lap).

2 is the offset into the range of the vlookup for the return valve

false - something to do with exact match or something.

What you probably want is:

=VLOOKUP($O3,$S$1:$T$17,2,FALSE) +$P3 +$Q3

Then drag that down for the all the cells you need to reference. O3 will become O4, O4, O5, etc while all the other cells (apart form P & Q) will reference the same cells (required for your vlookup to work).

I have a spreadsheet, that I designed with some tips from the guys on this forum, does something similar for a different type of sports club, but should be easily adapted for your needs.

If you can PM me with an email address I will happily send it back

Thanks for the help and offers guys, I think I have sorted it now

Ok I need another instruction if you would be so kind !

I have 2 colums, names in one, that persons championship points (referenced from the cell with total points eg, with =$B3) in the adjacent cell.

I can order the columns by "order largest to smallest value", but it only moves the name, not the points.

How do I achieve this ?

Ok I need another instruction if you would be so kind !

I have 2 colums, names in one, that persons championship points (referenced from the cell with total points eg, with =$B3) in the adjacent cell.

I can order the columns by "order largest to smallest value", but it only moves the name, not the points.

How do I achieve this ?

Easiest way to do this is select both columns and select the filter. You will then see a little drop down arrow in the top row. Click this and you can sort either colume higher or lower. Also can select specific names etc.

I don't know what I am doing wrong but I get the same result but now hidden !

What I am trying to achieve is; the names in one column and points in the adjacent one, auto arranged in assending points order and all displayed.

Height light your table and then click data > sort and select column 2 descending... That should work