Pivot table and array formula to sum total Q'ty

Pivot Table in excel is quite useful to show a table in various views as we want. It can give us total quantity by each product type. However, if we would like to get only the summation of Q'ty for certain product type, using of array formula will give us the result without creating any pivot table.

To create a Pivot Table, select the database table > INSERT > PivotTable

Choose the fields and we'll get the summation of Q'ty by Type.

However, if we would like to get the total q'ty of desired inventory type without creating the pivot table first, we can do that using the array formula.

Write the array formula as follows.

=SUM((Array1)*(Array2)) -- Just for explanation

=SUM((D4:D13=H11)*(E4:E13)) -- Real formula

But don't just press ENTER since it will be only a normal formula which will have an error. We have to press CTRL+SHIFT+ENTER and we will see the braces { and } at the beginning and the end of the formula.
We will see like this: {=SUM((D4:D13=H11)*(E4:E13))}

Please note that we can't is just type { and } in the formula!!

For this example, Array1 contains 0 and 1 according to the comparison between Type and Desired Type (cell H11)

Watch the video to see how we do all steps.

How to draw a circle in Excel

Microsoft Excel has various graphing options. The x-y chart allows us to draw any points or lines and it will automatically connects those points  to form a curve. We can use x-y chart to create a circle which can change its radius and center point.

To draw a circle in excel, first we have to prepare a table for x and y from angle (f) = 0 to f =360 deg. However, we have to modify the formula since Microsoft Excel uses radian instead of degree to calculate value of sine and cosine.

To convert angle from degree to radian, we use

Angle (rad) = Angle (deg.) x pi/180

Value of pi in excel can be referred as PI()
Therefore the formula of x and y become...

x= rcos(fxPI()/180)
y= rsin(fxPI()/180)

To offset the center point, the final formula becomes

x= rcos(fxPI()/180)+xc
y= rsin(fxPI()/180)+yc

In excel, after we write a formula in one cell, when dragging to other cells, it will change the cell reference. We can avoid this if we would like to let excel point to only desired cell by changing the reference with $ symbol by just repeatedly pressing F4.

Example:
Formula of x: write  =$D$3*COS($B8*PI()/180)+$D$4 instead of writing =D3*COS(B8*PI()/180)+D4

Using $D$3 will always refer to cell D3 which is the value of a radius.
If we write D3, after dragging the formula to other cells, the value of a radius will change to cell D4, D5, D6, ... which is wrong.

How to create Sub procedure and Function in Excel VBA

Microsoft Excel has a built-in VBA (Visual Basic for Application) editor that allows us to create sub procedure and function to do some calculations or other automation tasks.


This post quickly shows how to enable a developer ribbon bar and creating sub procedure and function.

To enable the developer ribbon bar in excel, just follows the steps below.

1. Select File
2. Select Excel Options
3. Select Customize Ribbon
4. On the Main Tabs, select Developer and you'll see the developer ribbon bar

Once the developer ribbon appears on excel ribbon bar, click at "Visual Basic" Button to enter the Visual Basic editor. However, you have an alternative choice just to use a shortcut "Alt+F11".

In Visual Basic editor, you'll have to add a module which will contain VBA codes. To do that just right-click at the VBAProject (Book1) and select Insert > Module.

Please note that Book1 is the current filename of excel. If you save the workbook as another name, then you'll see the different name.

To create a sub procedure, we have to follow the below syntax.

Sub SubName()
    ...
    ...
End Sub

Once the sub procedure called "test" is ready, you have to assign macro to a button that calls this sub procedure. To do so, switch back to spreadsheet and create a rectangle and right-click > Assign macro > Select sub procedure named "test" and click OK. The rectangle now becomes a button. Once you click it, it will call sub test to run and it will display the message Hello World!.

Not only a sub procedure can be added in the module, but also a function. Function returns the value and we often make it to do some calculations that you can't do with the built-in excel spreadsheet functions. However, in the example we're going to make a simple multiply function just to see how it's created and how to use.

Switch back to VBA editor (Alt+F11) and put the following codes.

Function multiply(a as single, b as single)
    multiply=a*b
End Function

The explanation to this code is simple. This function requires 2 inputs as number (a and b) and the function will return the value of axb.

To use the function just created, switch back to spreadsheet and type in any cell the formula to call this function: =multiply(D9,D10) where D9 and D10 refer to cell D9 and D10 respectively. Excel then call multiply function and automatically calculate the value once value in cell D9 or D10 changes just like other built-in excel spreadsheet functions.