After playing cards, you will count points of all the cards you have at hand. Ace is 14, king 13, queen 12 and jack 11. Number cards have the value according to number. The one who has the highest or lowest score is the winner.

It is easy to count but only picture cards have letters, which need to be converted to numbers.

Excel can help you or at least make counting a bit faster.

First we are introducing the variables. Z_val is the value of cards. Z_start is the address of the starting cell address. Z_val is set to zero. The program starts going downwards the cells. If the cell contains A then 14 is added to  z_val,  K is 13, Q is 12 and J is 11.

The number cards have their own value, like 6 is 6 and 7 is 7. To define which cell contains the numeric value, we can use Excel function ISNUMBER. In Visual Basic we can use Excel functions with WorksheetFunction extension.

The macro goes downwards cell by cell adding the sum value z_val, till the macro hits the first empty cell. Then macro jumps back to start cell and enter the sum value z_val into cell one above the start cell z_start.

Here is the macro:

Sub z_cards2()

Dim z_val

Dim z_start

z_val = 0

z_start = ActiveCell.Address

Do Until ActiveCell.Value = “”

If ActiveCell.Value = “A” Then

    z_val = (z_val + 14)

ElseIf ActiveCell.Value = “K” Then

    z_val = (z_val + 13)

ElseIf ActiveCell.Value = “Q” Then

    z_val = (z_val + 12)

ElseIf ActiveCell.Value = “J” Then

    z_val = (z_val + 11)

ElseIf WorksheetFunction.IsNumber(ActiveCell.Value) Then

    z_val = (z_val + ActiveCell.Value)

Else

    z_val = z_val + 0

End If

ActiveCell.Offset(1, 0).Select

Loop

Range(z_start).Select

ActiveCell.Offset(-1, 0).Select

ActiveCell.Value = z_val

End Sub

In B6 we have a value which does not have any value in our calculations as there is no Z-card in playing cards.

Place the cursor in B4 and execute the macro.

Z-card was not counted. If you mistakenly enter a wrong letter, the macro can handle that by passing the cell.

Excel macro was counting the points for each player. Of course, you could have done that manually too, but Excel can be used also for counting playing card points.

A list of sales reps and sales amounts. Which ones of sales reps have sales amount higher than average sales.

One way is to write an IF sentence.

Sentence in D3 is:

=IF(C3=(AVERAGE($C$3:$C$12));”ave”;IF(C3>(AVERAGE($C$3:$C$12));”above”;”below”))

If value is equal to average value, then the result is ave, if value is not average and if the value is above average then the result is above, if the value is not in average and it is below the average, then the result is below.

The conditional formatting has also feature above and below average. If you select the data range C3:C12 and select home – conditional formatting – top/bottom rules – above average, then the values higher than average are highlighted.

The cells above the average are coloured. The same cells are highlighted as with IF sentence.

You can count the values above average with MS Access.

Table Ave was created.

The table consists of sales rep and sales amount. ID field is a running number.

Same data is entered as in Excel was keyed in Access.

An SQL query is written to detect the values below and above average. The logic is same than in Excel. In Access if is IIF.

SELECT sales,

IIF(sales = (SELECT AVG(sales) FROM ave), ‘avg’, IIF(sales > (SELECT AVG(sales) FROM ave), ‘above’, ‘below’))

FROM ave;

The results are the same as in Excel.

When comparing these three methods, I think that conditional formatting is the easiest and fastest way to calculate which values are above or below average. No manual logic is needed when conditional formatting is used. Normally, Access is not used for calculation like this, but SQL is a very powerful tool for many analytical tasks.