Im so dumb this one will be easy I hope

**Im so dumb** · 03-05-2005, 08:45 AM

Hi, Just been trying to use Microsoft Excel 2003 to work out results for a non-profit motor racing org.(Ill keep it simple, I think?

)

WHAT I HAVE:
I have drivers names in column "A"(columns going down the page)
I would like column "E" to represent the finishing positions a driver recieved. IE:1st, 2nd, 3rd, 4th, 5th etc.
And column "F" to represent the points gained from their finishing position. EI:1st=25points, 2nd=20points, 3rd=15points, 4th=10points, 5th=5points etc.

WHAT I WANT TO DO:
Is there some formular(?) or function(?) or format(?) I can use to simply enter a
EXAMPLE#1 "1" in column "E" (meaning that driver finished 1st in the race ) and "25" (meaning the driver receives 25points for finishing 1st) will appear in the "F"column auotmatically? Or enter a
EXAMPLE#2 "4" in in column "E" (meaning that driver finished 4th in the race ) and "10" (meaning the driver receives 10points for finishing 4th) will appear in the "F"column auotmatically?

I only need an example for one driver (the first driver is entered in to row "5")and the points and race finishing positions I have given, and I can apply to all drivers,all finishing positions and all points. I guess this formular(?) or function(?) or format(?) needs to have all possibilities in it? Meaning all possible finishing positions and all points relating to those finishing positions.
My first driver listed is in row "5"

If you understand all that you are already 10 times smarter than all my friends

I hope you can help?!

I didnt pick a name like "Im so dumb" for no reason

**Cutter** · 03-05-2005, 10:12 AM

So you plan on entering the finishing position manually in Column E?

OK, there's more than one way to get the points to show up automatically.

One way is to set up a table somewhere on the sheet or on another sheet in the workbook. Put the positions in one column and the points in the next column. I'll use column X for position and Y for points just for example. In F5 put

=vlookup(E5,X$1:Y$10,2,false)

Copy this formula down. If there is a position that receives no points, I'll assume 6th and up, you could allow for that with this

=if(E5>5,0,vlookup(E5,X$1:Y$10,2,false))

Another way is to use the CHOOSE() function
In F5 put

=choose(E5,25,20,15,10,5)
Again you can adjust for no points if position exceeds a certain number, like

=if(E5>5,0,choose(E5,25,20,15,10,5))

**Im so dumb** · 03-06-2005, 07:46 AM

"So you plan on entering the finishing position manually in Column E?" Yes

I Decided to use this :

" In F5 put

=if(E5>5,0,choose(E5,25,20,15,10,5)) "

Was very helpfull thanks Cutter!!

But now F5 says " #VALUE! " ( infact every cell that I have entered a formular into says " #VALUE! " but the formular seems to work ok! why dose it say #VALUE! ?

I have also copied that formular into I5 , L5 , O5 , R5 , U5 & X5

In AA5 I have put

=SUM(F5,I5,L5,O5,R5,U5,X5) because I want to add all points drivers have earned in F5,I5,L5,O5,R5,U5,X5 together and have them Auto total in AA5 but it seems that if one or more of the cells (F5,I5,L5,O5,R5,U5,X5) is empty it wont keep a running telly (which is what I want) it seems to need all of the cells (F5,I5,L5,O5,R5,U5,X5) to be filled in before it totals. How do I get it to keep a running telly if some of the (F5,I5,L5,O5,R5,U5,X5) cells are blank?

**Cutter** · 03-06-2005, 12:08 PM

You're welcome. I'm glad I could help.

You get a #VALUE result if the race position is not filled in.

To account for an empty cell amend the formula to this in F5

=IF(E5>5,0,IF(E5="","",CHOOSE(E5,25,20,15,10,5)))

Your sum() function should work as you have written it.