Generating Random Times, Dates, Datetimes

We can quite easily generate all kinds of random numbers using Excel functions like RAND or RANDBETWEEN. The same functions allow us generate also random time and date, but the formulas involved are a bit more complicated. Here are some examples of values calculated with formulas provided beneath the table.

The following formulas have been used for times and dates shown in the five columns (from left to right):

=TEXT(RAND(),"HH:MM:SS")

=TEXT(FLOOR(RAND(),"00:00:05"),"HH:MM:SS")

=TEXT(RAND()*(15-14)/24+14/24,"HH:MM:SS")

=TEXT(RANDBETWEEN("2024-07-01","2024-12-31"),"YYYY-MM-DD")

=TEXT(RAND()*("2024-12-31 12:00:00"-"2024--07-01 12:00:00")+"2024-07-01 12:00:00","YYYY-MM-DD HH:MM:SS")

3D Graphics in Excel - Harmony unlimited

Would you like to create Excel charts similar to these presented here? Let me know - I could help...

Shapes Based On Johan Gielis's Superformula

The Gielis's Superformula is used to model variety of abstract and natural shapes. It finds application in biology, technology, mathematics, and physics.

I'm providing here a small representation of shapes created with my relatively simple VBA macro listed at the end of this post. You can experiment with different values of the parameters a,b,m,n1,n2, and n3 to get all kinds of shapes and find some useful models. To control the resolution of your shapes you can adjust the number of points, nP. Optionally, I've applied some Excel formatting of the plots.

 

  

Sub SuperformulaPlot()
'Generates Gielis's Superformula plot
'Calculates X and Y values and assigns them to the chart series
'Creates a 2D scatter plot; no markers
Dim nP As Integer, i As Integer
Dim xVal() As Double, yVal() As Double, theta As Double, r As Double
Dim a As Double, b As Double, m As Double, n1 As Double, n2 As Double, n3 As Double
Dim chartObj As ChartObject
nP = 500    'You can adjust the number of points to increase/decrease the resolution of the curve
a = WorksheetFunction.RandBetween(1, 5) 'Random parameters a,b,m,n1,n2,n3; experiment!
b = WorksheetFunction.RandBetween(1, 5)
m = WorksheetFunction.RandBetween(1, 10)
n1 = WorksheetFunction.RandBetween(1, 10)
n2 = WorksheetFunction.RandBetween(1, 20)
n3 = WorksheetFunction.RandBetween(1, 10)
'a = 1: b = 1: m = 2: n1 = 5: n2 = 5: n3 = 5    'just some default values
ReDim xVal(1 To nP)
ReDim yVal(1 To nP)
For i = 1 To nP
    theta = 2 * WorksheetFunction.Pi * (i - 1) / nP
    r = (Abs(Cos(m * theta / 4) / a) ^ n2 + Abs(Sin(m * theta / 4) / b) ^ n3) ^ (-1 / n1)
    xVal(i) = r * Cos(theta)
    yVal(i) = r * Sin(theta)
Next i
'Create a 2D scatter plot
Set chartObj = ActiveSheet.ChartObjects.Add(Left:=120, Width:=500, Top:=10, Height:=400)
With chartObj.Chart
    .ChartType = xlXYScatterSmoothNoMarkers
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Values = yVal      'Add data to the chart
    .SeriesCollection(1).xValues = xVal
    .HasLegend = False
    .HasTitle = True
    .ChartTitle.Text = "SuperPlot "& a & "-"& b & "-"& m & "-"& n1 & "-"& n2 & "-" & n3 & "; "& nP
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = "X"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Y"
End With
End Sub

Charts Based On Parametric Equations: Sinusoidal (Oscilloscope-type) Curves

Curves that can be used in physics for analysing pairs of simple harmonic motions are generally known as Lissajous Curves. They describe the superposition of two oscillations taking place at the right angle to each other,  at different frequencies. Their main application can be found in oscilloscopes. These instruments allow observation of two superimposed sine waves coming from varying signal voltages of sinusoidal nature. Depending on the frequency and amplitude of each wave, and the phase between them, we can see interesting patterns emerging.

Because the superimposed two perpendicular oscillations can be described with parametric equations for x and y in the Cartesian coordinate system, we can quite easily demonstrate the resulting shapes graphically using Excel charts. Let's look first at some of "oscilloscopic" patterns generated with Excel.

Shapes Based On Equations: Cardioids

Cardioid curve is formed by tracing a point selected on the circumference of a circle, rolling onto another circle of the same radius. Here are two examples of cardioid graphics, one is just a single cardioid curve and the other is presentation of 4 cardioids positioned within the same coordinates.

These kind of graphics can be generated using the VBA macro listed below the two charts.

Shapes Based On Parametric Equations: Epitrochoids

Epitrochoids are curves generated by a point selected on a circle of smaller radius rolling around the outside of a fixed circle of larger radius, and that selected point can be chosen at some distance from the center of the smaller circle. These are then 3-parametric curves. They can be helpful in creating decorative designs and other art applications, and can serve also as pastime and recreation for children. Since 1965 there is available a toy (geometric drawing device) known as spirograph, combining mathematics and art. It allows producing physically numerous curve designs based exactly on epitrochoid and hypotrochoid parametric equations.

However, nowadays the epitrochoids (as well as hypotrochoids - see another post at https://draft.blogger.com/blog/post/edit/3323809043368251287/9189797010242374274?hl=en-GB) can be generated quite easily in Excel by using VBA macro like the one provided at the end of this post.

Here are some examples of charts showing epitrochoid curves generated with the macro. Some of them present combined double curves.

Shapes Based On Parametric Equations: Hypotrochoids

Hypotrochoids are curves generated by a point selected on a circle of smaller radius rolling around the inside of a fixed circle of larger radius, and that selected point can be chosen at some distance from the center of the smaller circle. These are then 3-parametric curves.

You don't need any special programs, like e.g. Mathematica, to create hypotrochoids. They can be generated in Excel quite easily by using VBA macro like the one provided at the end of this post.

Here are some examples of charts with generated hypocycloid curves, both single and combined double curves:

Shapes Based On Parametric Equations: Hypocycloids

Hypocycloid is a parametric curve traced by a fixed point on a circle that rolls on the interior of another larger circle. 

The curve - due to its parametric functionality - finds many applications, both theoretical and practical. They include mechanical and construction engineering, e.g. design of gears, cams, valves, robotics (motion planning), and machine/structures design in general. Contribute also to aesthetically pleasing art designs and animations.

The hypocycloid curves can be generated quite easily in Excel with VBA macro,  presented at the end of this post. Here are some examples of charts with generated hypocycloid curves, both single and combined double curves:

Shapes Based On Parametric Equations: Epicycloids

Epicycloid is a parametric geometric curve obtained by tracing the path of a chosen point on the circumference of a circle (outside of it). Variety of epicycloid curves find applications in mechanical and construction engineering, e.g. construction of gears, cams, valves, pendulum clocks, robotic actuators, and machine/structures design in general. Contribute also to making designs of arts and animations.

In this context, it is useful to know how to generate epicycloid curves. In this post I'm presenting some of the curves along with the Excel VBA macro for creating this kind of curves. You can try to use it for your own creations. Just copy it to one of the modules in your workbook sheets and experiment with different settings and parameters.

Here are some examples of charts with epicycloid curves, single and double plots. 

 

Curves Based On Equations: Lemniscate of Bernoulli

The Lemniscate curve is based on specific algebraic polynomial equation. It has a shape similar to the numeral 8 and to the infinity symbol ∞ (called also a ribbon). You can create it with Excel VBA code and use it as one of the elements of graphic design.

Here are two examples of the curve created with the macro code listed below them.

Excel Comments: Setting Font Format

Default font format in Excel Comments may look like this:

If you want to change some of the font settings (i.e. the Windows font settings), you can either try to adjust font format in "Display settings" of Windows or use VBA code listed below. The second option is better, because it doesn't change font settings in all other Windows applications. Remember that you must make your Comments visible. If necessary, right-click in the cell and click on Show/Hide Comments option.

This is the event driven macro. To use it, you need to:

• open the Visual Basic for Applications (VBA) editor in Excel (use the shortcut ALT+F11 on your keyboard)
  
• click on the View in the top menu and choose Project Explorer option
• in your VBAProject double-click on Sheet1 and paste the macro code in the window
  
• close the VBA editor and return to your Excel worksheet

Excel Shapes: Positioning, Formatting and Layout In Worksheets

Excel Shapes are frequently used to enhance visual appeal of worksheets as well as to convey information. It's relatively easy to work with the Shapes. You can insert, resize, rotate, format, and position them within the worksheet, according to your needs.

This post is about special case of positioning and manipulating of Shapes. Namely, I'll show the way of inserting and moving them so that they are located/centred precisely at the crosspoint of 4 surrounding it cells. Furthermore, in this case I'll use a worksheet with squared cells. If you need to know how to make cells square please refer to one of my previous posts (Make cells SQUARE) .

Here are just two examples showing what I'm talking about:

 

 

These are the formatted Shapes called "Donut" and "Gear with nine teeth". They are placed exactly at the point where the four cells meet together. It's not that easy to do that by hand, so I've created the following Excel macro, to handle this task swiftly:

Prime Numbers: Their Importance In Computational Security and Other Applications

Prime numbers primarily constitute the basis of cryptography (hidden writing) and cybersecurity, i.e. secure transmission and storage of digital data. They are used in mathematical algorithms that enable efficient encryption (converting into code) and decryption (converting to plain text) of data.

And everything starts with generating and selection of just two prime numbers. However, the generated primes must be really very high numbers for the purpose. The higher they are the better becomes security/privacy of communication between parties.

Beside that, the whole concept of cryptography relies also on the complexity of applied algorithms (ciphers). And these are still based on prime numbers and are evolving all the time.

Cryptocurrencies, such as Bitcoin, are also created with with cryptographic algorithms and rely on prime numbers for ensuring secure transactions and protecting private information.

Primes are used as well in mechanical design, machinery and engineering structures. Choosing primes for e.g. the number of gear tooth, blades, or similar structural elements, helps to ensure reduction of the wear of materials, reduction or better distribution of vibrations, eliminating unwanted resonances.

In machine design - uniform gear wear, reduced noise or desired gear ratios can be achieved by choosing the tooth counts to be co-primes (relatively prime), i.e. 7 & 12, 13 & 21, or 7 & 9 & 12. This is an example of gear with co-primed  numbers of cogs: 7 & 12.

 

 

In electrical engineering - prime numbers are used in the design of power distribution system to improve the efficiency of the system.

There are also dozens of important uses for prime numbers in many sciences. So, if you are really interested and want to be better educated in the subject of primes and related to them algorithms it's worth to familiarize yourself with one of the most stimulating recent publications by Gary William Croft: "The Prime Spiral Sieve". It can be found at https://www.primesdemystified.com .

Prime Numbers: Presentation of Distribution of Primes with Waterfall Charts

One way of effective visual presentation of prime numbers is using Excel charts. And the Waterfall type of chart seems to be quite useful. It shows a running total as values are added to your data set; in this case - as consecutive primes are added. The running totals can be shown on the vertical axis as well as on the chart itself (for each subsequent prime value).

Here are some examples of this kind of visualization of running totals of primes. You'll need to enlarge the charts or use great size screen area in order to see the charts in detail.

Prime Numbers: How to Find the Highest Prime Number Below Given Limit

If you need to find/generate the highest prime number not exceeding some given limit, like e.g. 100,000, using the following Excel VBA macro is the appropriate and easy to use tool; obviously, within the Excel program specifications.

Just copy and paste the code into one of the modules of your workbook. Some explanatory notes are included within the macro.

Sub CalcTopPrimeNumberBelowGivenLimit()
'Max prime <1,000,000: 999,983
'Max prime <10,000,000: 9,999,991
'Max prime <100,000,000: 99,999,989
'Max prime <1,000,000,000: 999,999,937
'For max. integer handled by my computer (=1,068,699,999) the highest prime is: 1,068,699,979
    Dim n As Long
    Dim primes() As Boolean
    Dim i As Long, j As Long
    Dim tPrime As Long
    'Set the maximum limit for prime number generation
    n = InputBox("Enter your max. limit for the prime generation:", "Set the max. limit", 199999999)
    'Initialize the array to assume all numbers are prime
    ReDim primes(2 To n)
    For i = 2 To n
        primes(i) = True
    Next i
    'Apply the Sieve of Eratosthenes algorithm (it finds all primes up to a given limit)
    For i = 2 To Sqr(n)
        If primes(i) Then
            For j = i ^ 2 To n Step i
                primes(j) = False
            Next j
        End If
    Next i
    'Find the highest prime number
    For i = n To 2 Step -1
        If primes(i) Then
            tPrime = i
            Exit For
        End If
    Next i
    'Display the result
    MsgBox "The highest prime number below " & n & " is " & tPrime
End Sub 

 

Prime Numbers: How to Check If a Number is Prime Number

Prime number is defined as a whole number > 1 (integer) that cannot be divided by any whole number other than itself and 1.

Sometimes you may need to know if some specific positive whole number is a prime number or not.

At first look we can tell that given number is NOT a prime, if it is >5 and its last digit is equal 0,2,4,5,6 or 8. All primes >5 end in 1,3,7 or 9.

While its easy to identify primes within low range of integers (let's say, up to 100 or 500), it's becoming harder as we want to deal with higher range of numbers. Here are the lowest 81 prime numbers:

Looking for and Formatting Cells with Different Contents In One Pass

To find and select (and optionally, to format) cells that contain specific information or meet specific conditions we would usually use Go To command. That's simple: on the Home tab click Find & Select > Go To (or use shortcut: CTRL+G). When we click Special in the displayed Go To Special dialog box, we get many options to choose from; can look for Comments, Constants, Formulas, Blanks, Objects, Last cell, Conditional formats, etc.

However, we may need to use a completely different option. What if we'd like to find simultaneously (in one pass) for e.g. two or more different numbers or strings? There is no such option in the dialog box, so we have to find out a different approach.

Here's an example; I'm using the Excel macro listed below to find and fill with different colors all cells (in the specified range) containing four specific values. I need only replace the default values "a,b,3,4" with my own; here I've used "lost,123,mat,cat":

Sub HighlightSpecificCells()
'Applies different fill colors to cells containing different specified values
'This example fills cells containing four different strings/values
Dim rng As Range
Dim cell As Range
Dim inp As String
Dim vaArray() As String
Set rng = Range("A1:Z100")  'Change the range to the desired one
inp = InputBox("Enter values, separated by comma, you want to find in the specified range", _
    "Find and format cells that store specified values", "a,b,3,4")
vaArray = Split(inp, ",")   'Split the string using comma as the delimiter
'Loop through cells of the range
For Each cell In rng
    If cell.Value = vaArray(0) Then cell.Interior.ColorIndex = 4
    If cell.Value = vaArray(1) Then cell.Interior.ColorIndex = 6
    If cell.Value = vaArray(2) Then cell.Interior.ColorIndex = 24
    If cell.Value = vaArray(3) Then cell.Interior.ColorIndex = 44
Next cell
End Sub

This way, in a single pass you find your all specified values and fill the cells with the specified different color for each value.

This is just an example. You can modify the macro to meet your specific number of values, choice of colors, and whatever else you need.

 

Marching Squares Image

Created with Excel VBA (example):

If you have generated similar images in Excel, can you share them with me...😄

 

Drawing and Connecting Any Graphical Elements

In Excel, you can create diagrams, flowcharts and other visual aids of quite high complexity, without using any dedicated graphic design software. All you need for this purpose is utilizing the Insert tab and the Illustrations group within it. The Illustrations group includes pictures, shapes, icons, 3D Models, SmartArt, etc.

And within Shapes there is a group of Lines. Most of them are Connectors that can be attached to any of the illustrations and connect them with any others. The last three lines in the group, namely Curve, Freeform Shape, and Scribble, are not Connectors, but allow you to enrich the Excel collection of graphical creations with your own customized shapes/drawings.

Here's an example of possibilities offered by precise connections of the shapes. Just a simple idea:

Instead of adding your graphic elements one by one, you can choose from available selection of SmartArt graphics. Once you insert all your graphic elements in a desired order/arrangement, you can connect them by following these steps:

• select appropriate connector from the Lines collection
  
• click anywhere on the first graphic element; connection points will appear for you to choose one of them and attach the connector line
• drag the cursor to a connection point on the second connection object
• to adjust appearance of the line, you can right-click on it and choose "Format Shape" to modify the line colour, thickness, style etc.

You can also add text/labels to your graphic elements by right-clicking on them and selecting "Edit Text". The elements can be repositioned if needed, and the connectors will remain attached to and moved with the graphics. Pretty convenient.

Creating Shapes, Graphics, Illustrations - Part 2

Here is yet another way of creating graphics in Excel - using VBA macro, like this one:

Sub DrawDefShape()
'Draws any defined shape representing Bézier curves
Dim x As Integer, y As Integer
Dim pt(1 To 7, 1 To 2) As Single
Dim rng As Range
Set rng = Sheets("Data").Range("A2:B8")
'Fill array of control point coordinates with values
For x = 1 To 7
    For y = 1 To 2
        pt(x, y) = rng.Cells(x, y).Value
    Next y
Next x
Set myDoc = Worksheets("Data")
myDoc.Shapes.AddCurve SafeArrayOfPoints:=pt
Range("A10").Select
'Display the whole 2D array in MsgBox
For i = 1 To UBound(pt)
dPts = dPts & pt(i, 1) & "   " & pt(i, 2) & vbNewLine
Next i
MsgBox dPts
End Sub

Before going into the details of the macro structure and its use, let's look at some examples of curves and shapes you can easily create with it. Here they are:

Creating Shapes, Graphics, Illustrations - Part 1

Although Excel is not designed as a comprehensive graphic design tool, we can create in Excel all kinds of shapes/illustrations, actually without limits. There are many ways to do that, and the main methods are:

• Obviously - charts: while primarily used for data visualization, the charting capabilities of Excel can be utilized to create simple illustrations. By manipulating the data  (applying various conditional formatting rules) and using other formatting options, you can create visually appealing illustrations within the charting framework.
• Inserting predefined shapes: Excel provides a variety of predefined shapes that you can insert from the "Insert" tab on the ribbon. These shapes include rectangles, circles, arrows, lines, and more. They can be customized in terms of size, color, rotation, and other formatting options.
• Using drawing tools (to draw shapes): Excel allows you to draw custom shapes using the "Shapes" tool. You can choose from different shapes, such as polygons, curves, and freeform shapes. By selecting the "Draw" option, you can manually create the desired shape by clicking and dragging on the worksheet.
• Using SmartArt: this feature feature allows you to create diagrams, flowcharts, and other visual representations. SmartArt provides a variety of predefined layouts and styles, making it easy to create professionally-looking illustrations. You can access SmartArt through the "Insert" tab as well.
  
• Combining shapes: Excel also enables you to combine multiple shapes to create new ones. This can be done by selecting the desired shapes, right-clicking, and choosing the "Group" or "Combine Shapes" option.
• Editing shapes: Once a shape is created, you can further customize it by resizing, rotating, or changing its fill color, outline color, and other formatting options. This can be done by selecting the shape and using the available formatting tools.
• In addition, the Lasso Select tool is now available in Excel. This technique allows you to select non-contiguous cells or objects in Excel.

The following examples give some idea of how you can use these methods to design various graphic solutions and what you can create using Excel tools.

 

 

Excel Tips and Solutions for Every User

With over 110 posts published so far in my blog I've decided to summarize them in carefully edited eBook format (Kindle edition). You can have a look at it here .

It covers many less known Excel features and topics, from workbook design principles to quite sophisticated VBA programming tasks and some creative art.

If you are serious about your proficiency in using Excel program, this eBook may be worth to get it and keep it handy.

Here's the book cover:

 348 pages of creative ideas and solutions.

 

Creative Charts - Excel Art

These are some of my Excel creations: charts based on the equation of circle. Unlimited Excel...

 

How to Find the Greatest Common Divisor of any Positive Integers

The Greatest Common Divisor (GCD) is the largest integer that divides some two or more numbers (up to 255 positive integers) without a remainder.

In Excel, the fastest and most convenient way to find the GCD of any number of integers (up to 255!) is by using its GCD function. E.g., to find this divisor for any three integers you would use formula like this one:

= GCD(60,148,280)

which - for these specific values - results in number 4.

 

To find the GCD for just two integers you could also use this kind of Excel macro:

 

Sub FindGCD()

'Calculate Greatest Common Divisor (GCD) of two integers

Dim a As Integer, b As Integer, temp As Integer

Dim c As Integer, d As Integer

'Input the two integers

a = InputBox("Enter the first integer:"): c = a

b = InputBox("Enter the second integer:"): d = b

'Perform Euclidean division algorithm

Do While b <> 0

    temp = b

    b = a Mod b

    a = temp:

Loop

'Display GCD

MsgBox "The GCD of " & c & " and " & d & " is = " & a

End Sub

 

Jumping Across Cells with Enter Key

Normally, in Excel, when you press Enter key - depending on your initial selection of editing options - you move in one of the directions (Down, Right, Up, Left), as shown in the snippet, by just one cell. There are no other options available in Excel settings. I'm using the Down option for the purpose of this post.

So, what you can do, if for some reason you definitely need to move automatically and repeatedly from the current cell by more than one cell and in any selected direction?