Advanced formulae – Spreadsheets

In this tutorial, we seek to use the basic arithmetic operations addition subtraction multiplication and division to accomplish complex tasks.

Usually when using a computer system to represent addition subtraction multiplication and division we use the following symbols respectively:

Addition

The plus sign : +

Hold shift and press the plus sign
Subtraction

The hyphen: –

Hyphen Key
Multiplication

The asterisk : *

Asterisk key
Division

The front slash: /

Front slash key.

Tutorial:

Click the link below to download the file. Do not open the documents using Google Sheets, rather download the documents and open it with your desktop application of Microsoft Excel:

https://drive.google.com/file/d/1lubYKdD70AAx_9eD2BeoBb8FbjKcd9Wq/

This tutorial demonstrates how a spreadsheet can be used to perform a complex calculation. The complex calculation chosen is the quadratic formula.

Note that you do not need to have an intimate understanding of the quadratic formula. All you will need to do is to break down the quadratic formula into its sub parts, and represent those parts using functions and formulas in your spreadsheet software.

This Excel document contains two worksheets. Use the first worksheet to fill in formulas and functions which can be used to calculate the items shown in the labels.

How to fill the sheet:

A copy of the sheet is shown below:

Advanced formulae tutorial

Start filling in the correct formula for each level shown in the sheet. For example, you would start with the label negative B an insert a formula in the cell next to the label which would calculate the volume negative B. In this case you would enter =-1*C10 Into cell location H3.

Continue following all the way down for b squared, 4ac …. All the way until you have calculated root 1 and root 2.

The second sheet contains notes and solutions to the first worksheet.

Homework

Complete the challenge listed in the sheet above. Ensure that you do not use any pre-calculated values.

Video Tutorial

Updated 2021 November 8th

-Fixed a spelling error

-Improved formatting

-Added Tutorial Video

© 2020  Vedesh Kungebeharry. All rights reserved. 

Solution to Queue Exercise

Implementation using flowgorithm

Download the file here: https://drive.google.com/file/d/1adiH9SG9vbXVNBydWC5tKR1_9i9y9ZPj/view?usp=sharing

Coded Implementation in C

Code is split into 3 files:

main.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main()
{
    char userInput[5];
    int data = -9999;



    puts("\nEnter a valid command: add, remove, peek , exit\n");
    scanf("%s", userInput);
    while(strcmp("exit",userInput)!=0)
    {
        if (strcmp("add",userInput)==0)
        {
            puts("\nEnter data to add to the end of the queue\n");
            scanf("%d", &data);
            enqueue(data);
        }
        else if (strcmp("remove",userInput)==0)
        {
            data = dequeue();
            printf("\nremoved %d from the front of the queue\n",data);
        }
        else if (strcmp("peek",userInput)==0)
        {
            data = peek();
            printf("\npeeking at front : %d\n",data);
        }
        else
        {
            puts("\nInvalid command entered. Ensure that your command is in lowercase\n");
        }

        puts("\nEnter a valid command: add, remove, peek , exit\n");
        scanf("%s", userInput);
    }
    puts("\nExiting Program...\n");



    return 0;
}

Queue.h

#include <stdio.h>
#ifndef QUEUE_H_INCLUDED
#define QUEUE_H_INCLUDED

void enqueue(int newData);
int dequeue();
int peek();



#endif // QUEUE_H_INCLUDED

Queue.c

#define MAX_SIZE 5

int sizeOfQueue = 0;
int head = 0;
int tail = -1;

int queueData[MAX_SIZE];



void enqueue(int newData)
{
    if (sizeOfQueue==MAX_SIZE)
        puts("Error in \"void enqueue(int newData)\". Queue is full" );
    else
    {
        queueData[++tail]= newData;
        sizeOfQueue++;
    }


}

int dequeue()
{
    int result=-999;
    if (sizeOfQueue==0)
        puts("error in \"int dequeue()\". Queue is empty, -999 returned");
    else
    {
        result=queueData[head++];
        sizeOfQueue--;
    }
    return result;

}

int peek()
{
    int result=-999;
    if (sizeOfQueue==0)
        puts("error in \"int peek()\". Queue is empty, -999 returned");
    else
        result=queueData[head];


    return result;

}


© 2020  Vedesh Kungebeharry. All rights reserved. 

Queues Closure – Discussion

  1. Queues have a head an a tail, also called a front and an end.
  2. Queue operations are add, remove and peek . add and remove are also referred to as enqueue and dequeue.
  3. When a queue is implemented with an array, what are the values of the head and tail at initialization?
  4. The head and tail are updated during queue operations. During which operations are the following indices updated?
    (a) head
    (b) tail
  5. Explain what is meant by the acronym FIFO.

© 2020  Vedesh Kungebeharry. All rights reserved. 

Video Creation using Source files – Tutorial

Today, we’re going to create the following video from some source files:

Download the following files to practice the skills :

https://drive.google.com/drive/folders/1f3m2gXDaPThljZ_fmc6OGMYzwpanht2Y

(the videos are free to use and were downloaded from https://pixabay.com/images/search/soccer/ )

Follow along with the tutorial video below to create a video from the source files above:

Tutorial Video: Video Creation with Video Editor in Windows 10

Remember to pause the video when needed during the creation of the video from the source files.

Updated:

5th October 2021 – Enhanced formatting

© 2020  Vedesh Kungebeharry. All rights reserved. 

Flowcharts – Sequence

Sequential instructions refer to groups of instructions which follow a strict order.  We can observe this by example:

Task: Create a program which prompts the user to enter two numbers.  The program must out put the  sum of the two numbers.

Solution in narrative form:

Prompt the user to enter the first number and accept the data.  Prompt the user to enter a second number and accept the data.  Calculate the sum. Output the sum to the user

Solution in Flowchart:

Notes/observations

  • Notice that to accomplish this task all steps are followed in sequence.
  • Variables are used as containers to store data input from the user and to store the results of processing for output. E.g num1, num2 for input storage, and result for storage and output.
  • the box which contains result <–num1+num2 can be interpreted as “adding the contents of num1 to num2 and then storing the value that was produced by processing in result
  • the line result <–num1+num2 can also be written as result = num1+num2

See the solution in flowgorithm below:

You can download the flowgorithm solution here:

https://drive.google.com/file/d/1s_QRRC-kx6Q-8NA3C__42xCn_-CpumA2/view?usp=sharing

Homework: Draw a flowcharts which accomplish the following tasks

1. Prompt the user to enter 3 numbers and output the sum

2. Prompt the user the enter 2 numbers and output the product

3 Prompt the user to enter 2 numbers and outputs the quotient (first number divided by the second number.) . What happens if the user enters the first number as 5 and the second number as 0 ?

Update

Updated on 18/11/2021 to include section “Notes observations” and 3 additional observations other than the first listed item.

© 2020  Vedesh Kungebeharry. All rights reserved. 

Flowcharts – Representation

Flowcharts are graphical representation of algorithms. They consist of shapes which contain instructions that can be followed. The order of execution of instructions is determined by arrows which start from the “Start” shape, move onto other intermediate shapes until execution stops at the “End” shape.

The shapes used are shown below:

We will observe how these symbols are used to represent the solution to a problem in our next post on Sequence.

Live Class

Algorithms review, Introduction to Flowcharts (Live Class – 2025-09-12)

Updates to this post

2025-09-12: Added live class section

© 2020  Vedesh Kungebeharry. All rights reserved. 

Formulas and Functions – Part 2 (Video)

This series of videos is a continuation of the post, Formulas and Functions – Part 1 (Class Video)

Use this file and follow along with the videos, pausing the video when you need to accomplish a task:

It 5 videos covering the following functions: Max, Min, Count, Countif, Rank, and Vlookup.

A 6th function, PMT was left for your research and homework.

See the videos below, and follow the instructions for homework based on the pmt function:

Max and Min

Count

Countif

Rank

Vlookup

PMT

Homework – Research/examine the PMT function.

Explain :

  1. The functions’ parameters.
  2. The purpose of the function.

We will be discussing this function at our next class.

© 2020  Vedesh Kungebeharry. All rights reserved. 

Spreadsheets: Introduction, definition & terminology

See the video below which introduces spreadsheet software

Terminology

Spreadsheet – an electronic document in which data is arranged in the rows and columns of a grid and can be manipulated and used in calculations.


Oxford English Dictionary via google. (https://www.google.com/search?q=define+spreadsheet&oq=define+spreadsheet&aqs=chrome..69i57j0l7.4183j1j7&sourceid=chrome&ie=UTF-8)

A Spreadsheet consists as a grid of many cells.

Cell – A single location in a spreadsheet that can store data. It is the intersection of a row and column

row – A horizontal arrangement of cells , usually denoted by a number

column – a vertical arrangement of cells ,usually denoted by a letter, cell,

value – the data that is stored in a cell.

formula – a representation for a calculation stored within a cell. The formula itself is not displayed, rather, the results of the calculation is displayed in the cell.

range – a group of cells; can be a group of horizontal cells, a group of vertical cells, a block of cells, or even many different blocks of cells.

worksheet – a single spreadsheet in a workbook.

workbook – a file , consisting of many worksheets (individual spreadsheets). A workbook is a file which is comprised of 1 or more worksheets (spreadsheets)

(last updated Monday 11th January 2021)

© 2020  Vedesh Kungebeharry. All rights reserved. 

Queue Exercise

Implement a Queue with a maximum size of 5 elements using flowgorithm or a C program. The program must continuously prompt the user for an option:

add -if the user enters add, prompt the user to enter an integer and add it to the back of the queue.

remove -if the user enters remove, display the integer at the front of the queue and remove it

peek – If the user enters peek, display the integer at the front of the queue

exit – exits the program.

Use the following template in flowgorithm if necessary:

https://drive.google.com/file/d/1wFmReWDQbIYKV8z4kNXYvs9XarPIvnoo/view?usp=sharing

© 2020  Vedesh Kungebeharry. All rights reserved.