# Hidden value that can be calculated

#### Stomach

##### New Member
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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Jerry Sullivan

##### MrExcel MVP
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

##### New Member
Hello!

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

#### Stomach

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

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

#### Jerry Sullivan

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

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

Code:
``````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.

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

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

Replies
1
Views
151
Replies
4
Views
837
Replies
1
Views
393
Replies
0
Views
273
Replies
9
Views
250

1,195,582
Messages
6,010,580
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back