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

 

[Jerry Sullivan]

Hi and Welcome to the Board,

If you are using xl2007 or later, try this array formula entered into Cell U7 then copied down.

Array formulas need to be entered using a combination of Ctrl, Shift, Enter (not just Enter).
So, after pasting that formula with your cursor still in that Cell,
hold down both the Ctrl key and the Shift key, then hit Enter.

=SUM(IFERROR(CHOOSE(D7:T7,10,8,6,5,4,3,2,1),0))

 

[Stomach]

Hello!

Thank you for your fast answer!

It seems that everything works great. Thank you very much.

 

[Stomach]

I reply here, because it's the same table.

I want to have the average of the points of each driver. The problem is that there are the DNF's and I want that he count's it as 0 points. I've got this:
=AVERAGE(Table5[@[Column1]:[Column12]])

So for example: 1st race 8 points, 2nd race DNF = the average should be 4.

What do I have to put in the function?

 

[Jerry Sullivan]

It's probably most efficient to calculate the sum in one column, then divide that total by the number of races to get the average.

Assuming that the total number of races are the count of cells that have either a number or text such as "DNF" (but not blanks) then...

Use this Array formula to calculate total points in [Column13] (confirm with Ctrl-Shift-Enter)
=SUM(IFERROR(CHOOSE(Table5[@[Column1]:[Column12]],10,8,6,5,4,3,2,1),0))

Then use the worksheet formula (not an Array formula) to calculate the Average using the total in [Column13]
=IFERROR([@Column13]/COUNTA(Table5[@[Column1]:[Column12]]),0)

 

[Stomach]

Okay, thank you very much. I transformed the table, so there are now places and not points, and nearly everything works: http://oi46.tinypic.com/10x7rdz.jpg. Now, what do I have to change in the function, where I want to calculate the average place. I want that he calculates the DNF as the 22nd position. Any idea how to solve it?

The second thing is, is there any way to sum the cells where the number is underlined? In this case the underlined numbers are the Poles.

 

[Jerry Sullivan]

Here's an Array formula to calculate the average place (confirm with Ctrl-Shift-Enter)
=SUM(IF(Table5[@[Column1]:[Column12]]="DNF",22,Table5[@[Column1]:[Column12]]))/
COUNTA(Table5[@[Column1]:[Column12]])

Standard worksheet formulas can't differentiate underlined numbers. You could employ a VBA User Defined Function (UDF) to do that.
Let me know if you want some help with that UDF.

 

[Stomach]

The formula for average place works very well. Thank you.

As I am an rookie in the whole excel stuff, it would be nice, if you could explain to me the UDF thing.

 

[Stomach]

JS411 could you manage to do this, it would be very nice

 

[Jerry Sullivan]

Hi Stomach, Sorry to have lost track of this thread.

Try copying this code into a Standard VBA Code Module in your workbook.

Public Function AvgPlaceFromPole(rResults As Range) As Variant
'--calulates average place number for underlined values in range
'--"DNF" or other non-numeric values are counted as 22nd place
'  returns #N/A if range has no underlined values

    Dim c As Range
    Dim iSum As Integer, iPoleCount As Integer

    
    For Each c In rResults
        If c.Font.Underline = xlUnderlineStyleSingle Then
            iPoleCount = iPoleCount + 1
            iSum = iSum + IIf(IsNumeric(c), c, 22)
        End If
    Next c

    If iPoleCount = 0 Then
        AvgPlaceFromPole = CVErr(xlErrNA)
    Else
        AvgPlaceFromPole = iSum / iPoleCount
    End If
End Function

Public Function AvgPointsFromPole(rResults As Range) As Variant
'--calulates average Points based on finish place for underlined values in range
'  returns #N/A if range has no underlined values

    Dim vPoints As Variant
    Dim c As Range
    Dim iSum As Integer, iPoleCount As Integer
    Dim bBase0 As Boolean

    
    vPoints = Array(10, 8, 6, 5, 4, 3, 2)
    bBase0 = LBound(vPoints) = 0

    
    For Each c In rResults
        If c.Font.Underline = xlUnderlineStyleSingle Then
            iPoleCount = iPoleCount + 1
            If IsNumeric(c) Then _
                iSum = iSum + IIf(c > UBound(vPoints), 0, vPoints(c + bBase0))
        End If
    Next c

    If iPoleCount = 0 Then
        AvgPointsFromPole = CVErr(xlErrNA)
    Else
        AvgPointsFromPole = iSum / iPoleCount
    End If
End Function

Once that is in place you can use the two functions in your workbook very much like worksheet functions.

Add two columns to your table using these formulas...
=AvgPlaceFromPole(Table5[@[Column1]:[Column12]])

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