26 November, 2022

Create your own Lottery

Do you want to create and issue (distribute) randomly coded tickets to people - for any good reason you can think of? It could be e.g. some kind of a lottery or a game. Excel may be very helpful in achieving such a goal. 

Let's say you've decided to issue tickets marked with codes like this: A000 (one letter and three digits). This pattern creates 26,000 permutations, so you can issue 26,000 tickets with a different code each. Then, you build a list of only 5200 randomly selected codes in Excel worksheet (col. B in example shown below). You crate the list using this formula:

 =CHAR(INT(RANDBETWEEN(65,90)))&MID(RAND(),3,3)

and then copying all 5200 positions and paste them (in place) as values. You can remove from the list any recurring codes by selecting Data>Remove Duplicates (in col. B) in the ribbon.


This way you give the chance of selecting a winning ticket like 1:5 (on average).

When someone presents you one of the 26,000 issued tickets, you can easily check if the ticket code is present in the list of 5200 codes. If so, it means that it is a "winning" ticket. You do that by clicking on the control button "Check for Winners", as shown on the picture. The following Excel code (VBA macro) must be assigned to the button:

Sub CheckForWinners()
'Checks for winning lottery ticket codes
'and records the winning tickets in Col. A

    Dim cnt As Integer
    Dim rngA As Variant
    Dim rngB As Variant
    Dim cod1 As String
    Dim cod2 As String
    Set rngA = Range("A:A")
    Set rngB = Range("B:B")
    On Error Resume Next
repeat:
    cod1 = InputBox("Please enter your ticket code", "Check your ticket", "A000")
    If Len(cod1) <> 4 Then GoTo repeat
    cod2 = WorksheetFunction.VLookup(cod1, rngB, 1, False)
    If cod2 = cod1 Then
        MsgBox "Congrats! Your ticket is a WINNER.", , "Ticket " & cod1
        cnt = WorksheetFunction.CountIf(rngA, "*") + 1
        Range("A" & cnt).Value = cod1
    Else
        MsgBox "Sorry, your ticket is NOT a winner.", , "Ticket " & cod1
    End If
End Sub

Every winning ticket's code will be eventually recorded in col. A.

In this example, if you set the price of one ticket at $5, and the winner's prize at $20, you can earn in a long run $1 on each sold ticket... 😀

 

29 October, 2022

Excel Secrets: Dates in Charts

Something strange happens with Excel charts when you create a chart based on data that include column of dates in the selected data range. Here is an example of simple data set I'm using as basis for my charts:

This is my first selected type of chart. Everything looks OK, dates on X axis are displayed correctly:

Now, when I create (or switch to) some different types of my chart, here's what I'm getting:

The X axis presents here completely different dates in different steps from those listed in my table. Why is this happening? Does anybody know? Is this one of Excel tricks?

For now just be aware of this abnormality and examine your charts well before using/publishing them.

 

24 April, 2022

03 April, 2022

Workbook Events: Printing - Speach - Alerts

When printing in Excel, one quite frequently makes mistakes. Sometimes the printout does not look as expected and we may waste more paper than necessary. To reduce such outcomes to minimum we can utilize Excel event feature called BeforePrint.

You can use the following workbook event procedure (VBA code) that will - just before printing your worksheet/selection - alert you with speech feature by asking if you are sure that your workbook and the print settings are OK; if not, printing is cancelled.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 For Each wk In Worksheets  'Make sure that worksheets are recalculated before printing
    wk.Calculate
 Next
 vbOption = MsgBox("Are you sure that all settings are OK and print can be started?", vbYesNo)
 If vbOption = 7 Then  
'6=Yes, 7=No
    Cancel = True
    Application.Speech.Speak "Print is cancelled."
'    MsgBox ("Print is cancelled.") 'optional
    Else
        Application.Speech.Speak "Recalculation is completed and now printing takes place."
    End If

End Sub

To implement this procedure, select the Developer tab in the ribbon and select Visual Basic from the menu, then select the View tab>Project Explorer. In VBAProject of your workbook click on ThisWorkbook, then copy the code provided here and paste it into the space located directly under the Workbook field there. Save your workbook as Excel Macro-Enabled Workbook.

Try to print something to see if the event procedure works as intended.

 

25 March, 2022

Wonders of Excel: FILL HANDLE

As you probably know, Excel Fill Handle is located in the bottom right of any active cell or the bottom right cell of a selected range. It's a quite small black square visible in this snip:

Small it is, but it's very important to know how to utilize it, as it can be really helpful in eliminating lots of manual typing and saving time in working with data entry... and much more than this. It can be used for filling any range of cells with increments of any choice you want to select.

To utilize the Fill Handle, first you need to fill one or more cells with values (numbers or text, incl. dates and other custom lists). Hover your mouse pointer over the small square until it changes to a small black cross, then you have couple of choices: you can left-click, right-click or double-click (in some situations); click and drag the mouse in the direction you want to fill up the range of cells with series or custom lists that follow your pre-defined pattern.

24 February, 2022

Traps of Custom Formatting

Numbers can coexist in Excel cells with text strings. What may surprise you is that the contents of such cells can be treated and used as numbers. It means that they can be used as ordinary numbers in mathematical calculations, functions and formulas. This happens when you apply some kinds of custom formatting for cells.

Here is such example of cell formatting and some confusing results of using it in formulas involving text strings.


I've used the following custom format for cells in column A:

17 February, 2022

Picture in Picture in Excel Worksheet

What if you need to highlight/call attention to any fragment of your worksheet, be it a piece of data table or a picture/chart, for a presentation or just for printing a report?

You can do it quite easily in Excel. Let's consider a picture. You may need to overlay an enlarged small fragment of your picture onto the original whole picture. To do that you can use the Windows "Snip & Sketch" utility (the shortcut to invoke it, is: Windows Logo key + SHIFT + S). You can select either rectangular or free-form snip. After getting the snip, format/enlarge it as needed, in a way attracting attention to it, and move it to a desired position. This is an example of the 'picture in picture':

The same can be done also with any chart/graphics.