Hidden value that can be calculated

[Stomach]

Hello there!

First of all, sorry for my english - I'm from Poland. If something won't be clear, please ask.

So, first I'll show you an example. I want a table, that look smiliar to this one: 2006 Formula One season - Wikipedia, the free encyclopedia. It'll be table for a game. I want to write the positions in the cells and than that automatically the table will calculate the points for each driver. So for example for the 1st place - 10 points, for the 2nd place - 8 points, for the 3rd place - 6 points, and so on. That points should be in some way a value of a position I think.

Here is my table: http://oi48.tinypic.com/2qbrrt0.jpg. So I want to have the positions in the place where the points are, but I want to have the same effect - that the points were added up.

Is it possible? Any ideas?

Greetings

 

[Stomach]

Thank you for your reply, JS411.

First of all, it doesn't work for me It's returning with #NAME?. I'm an beginner in this VBA thing. If I'm right, I go to Developer -> Visual Basic -> and than from the projects I choose ThisWorkbook, and there I paste the formula (Excel 2010). Is this the right way?

The second thing is, that I unfortunately thing, that this isn't the formula I wanted - I think I described it wrong. Sorry for that. I want to count in cell H7 the number of poles gained from each driver. It should look like now, but with the difference, that I don't calculate it manually, but with this formula. The poles are underlined, as you can see in my table.

Waiting for you feedback.

Regards

 

[Jerry Sullivan]

Hi again,

The code needs to be placed in a Standard Code Module instead of the ThisWorkbook Module.

To do that, with your workbook already opened...
1. Copy the code, by highlighting the code and pressing the keys CTRL + C
2. Press the keys ALT + F11 to open the Visual Basic Editor
3. Press the keys ALT + I to activate the Insert menu
4. Press M to insert a Standard Code Module
5. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
6. Press the keys ALT + Q to exit the Editor, and return to Excel


I misunderstood and thought you wanted the Average Place and Average Points when a Driver Starts at the Pole.

Here is a UDF that can be used to get the Count of Poles Gained.

Public Function PoleCount(rResults As Range) As Integer
'--counts number of cells in range formatted single underline

    Dim c As Range
    Dim iPoleCount As Integer
    
    For Each c In rResults
        If c.Font.Underline = xlUnderlineStyleSingle Then
            iPoleCount = iPoleCount + 1
        End If
    Next c
    PoleCount = iPoleCount
End Function

Once the UDF is in a Standard Code Module, you can enter a formula like this into H7..

=PoleCount (Table5[@[Column1]:[Column12]])

 

[Stomach]

Hi JS411,

Thank you very much for all this, it works great and my table is getting perfect. It was very helpful.

According to out PMs, my next problems I'll share on a new thread. This one is finished for now.

Greetings