Formula Query

[AndyinEngland]

I need to be able to put a value in a cell which will then display another value. eg. I want to be able to enter "1" (finishing position in a race) and have the value "22" displayed (pts. awarded for finishing 1st)
Can anybody help? or at least give me some kind of solution!
Thanks.

 

[SteveInAlabama]

Please clarify: do you mean you want a "22" to appear in the same cell that you typed a "1" into?

 

[WillR]

set up a table with position in column 1 and points in column 2.(say range = A1:B10)

say you put your "place" in cell D1 - in your results area, in the cell next to your position (say E1), place the following formula...

=VLOOKUP(D1,A1:B10,2,False)

This will return the points value of a 1st place finish in the cell next to the place. Note that in your original table, the PLACES column (say P1 to P10) needs to be in ascending numeriacl order for VLOOKUP to work.

HTH

 

[AndyinEngland]

On 2002-09-02 07:08, SteveInAlabama wrote:
Please clarify: do you mean you want a "22" to appear in the same cell that you typed a "1" into?

Not necessarily . . . as long as I can add up all the points in a season when needed, the "22" could appear in another column.

 

[WillR]

Andy,

So have you tried my example, or do you need more detailed explanation.....?

 

[AndyinEngland]

will
will try your solution tonight, sounds like its what i want
Thanks alot

 

[chef]

simple formula would do..

eg if(A1=1,22,0)...

you could build this up so if you were second...eg16 points...would be
=IF(A1=1,22,IF(A1=2,16,0))and so forth

regards
chef

 

[WillR]

Chef.... maybe suck eggs info etc...but...you know you can only have upto 7 nested IF functions though, which is why i recommended the VLOOKUP option...formula less complex AND if points for positions change, only one table needs to be updated instead of all those formulas...

 

[chef]

Will,

Not taking away the usefulness of vlookup....was merely suggesting an alternative method..and sometimes a simple formula can work for simple tasks.
As for 7 if statements..you can actually get around this by using named formula..ie name your first 6 ifs say..test1...another 6 say test2....then if(test1,[action](iftest2...and so forth.
Don't really recommend using more than 7 if statements though and in that circumstance,yes vlookup or VB would be a more productive solution

regards
chef