Formula | Chapter 2 | Edate

'EDATE' formula

Edate is used to calculate due date or maturity date.
Syntax

=Edate(start_date, months)
start_date: denotes a date in date format.
months: denotes tenure of months of maturity.
We can quote this formula in different ways.
Say, we are going to calculate 12 months maturity.
A3 cell has date value 25-11-2018 (should be in a date format).
so the syntax is =Edate(A3,12)
Returns: 25-11-2019 (date of maturity).
Alternatively the syntax is
=Edate(Date(2018,11,25),12)
Returns the same value 25-11-2019 (date of maturity)

'EOMONTH' formula

It returns the serial number of the last day of the month before or after a specified number of months.

Syntax

=Eomonth(start_date, months)

start_date: denotes a date in date format.
months: denotes tenure of months of maturity.
We can quote this formula in different ways.
Say, we are going to calculate 12 months date.
A3 cell has date value 25-11-2018 (should be in a date format).
so the syntax is =Edate(A3,12)
Returns: 30-11-2019 (date of maturity).
Alternatively the syntax is
=Edate(Date(2018,11,25),12)
Returns the same value 30-11-2019 (date of maturity)

'HOUR' formula

Returns the hour of a time value, and the result varies between 0 to 24 only (whole number only....no 18.25). This formula works with decimal places. Say, in case of 1.25 it ignores 1 and works with .25 only.

Syntax 

=Hour(serial_no)

For instance

=Hour(1.25) 

returns 6

Again

=Hour(.25) 

returns 6

Actually .25 of 24 is 6........... (25% of 24 is 6)

 

ILL 1

Explanation:
Observe the ILL 1 carefully.

'ISOWEEKNUM' formula 

Returns the week number for the given date.

Syntax

=ISOWEEKNUM(date)

For instance

Cell A3 has date value  1-1-2019 in date format.

=ISOWEEKNUM(A3)

Returns 1i,e first week of the year 2019.

In case of  7-01-2019 formula returns 2.

'MINUTE' formula

The formula returns the minute of a time value. Minute is represented as integer, ranging from 0-59.

The formula accepts decimal places. No whole number.

Representing percentage.

Syntax

=MINUTE(serial_number)

For instance

=MINUTE("12:35 PM")

Returns 35

 

 

Formula | Chapter 1 | Date & Time

Formulas can be accessed by clicking at the 'Formulas' tab. 

Function key and 'Insert function' button serves the same purpose. 

'Formula bar' keep track of your formula. Not only formula, what you write in a cell is highlighted in the formula bar. 

When we click Insert Function / Function, Insert Function window pops open.

Insert Function Window

All type of formula resides in 'Insert Function' window. 
And if we click select a category combo box we can find the category of formula in excel.

Date & Time 

Interestingly Excel stores dates as sequential serial numbers.

And 1st January 1900 is the base year, and the serial is 1.

Accordingly, 1st January 2008 has a serial number 39448 since 1st January 2008 is 39447 days after 1st January 1900.

'Date' formula

Returns the number that represents the date in Microsoft Excel.

Syntax

=Date(year, month, day)

Example

Date(2019,03,23).

Returns 23-03-2019.

Date function

 'Datevalue' formula

Converts the stored date into serial number.

Syntax

=DateValue("text")

Example

Date("1-1-2008")

Returns 39448

This function is mostly useful where worksheet contains dates in text format.

'Day' formula

Returns the day value from 1-31. And it calculates accordingly.

Syntax

=Day(serial_number)

Example

Day(100)

Returns 9
In fact representing 10th April. 

'Days' formula 

Calculates number of days from a base date. It can calculate backward too. Again the base date should not be later than 1-1-1900. 

Syntax

=Days(end_date, start_date)

Days360 formula 

Us or European method of approximation of days calculation from a base date.
Syntax
=Days360(start_date, end_date, method)
There are two methods
False=US method of approximation of 30 days.
True= European method.

Formula | Auto Sum | Deduct | Multiply | Divide

This is the first dedicated post on Excel Formula.
To access formulas click Formulas tab.
Even we can start with formula under Home Tab too.

Under Home Tab

Excel is always formula ready. One can start dealing with formula at any space. 

Things to remember

• Formula always starts with =
• While writing formula ignore upper case or lower case criteria, just start typing. 
• Instead of typing the cell values while writing formula one can link or denote the corresponding cell by clicking at the cell. Say, want to multiply F2 to G2, only type = and then click at F2 cell then type * lastly link G2 by clicking at G2 cell. Simple......

Lets start

Sum 

How to sum up cells with values?
Formula Syntax
=SUM(B3:B14)
(B3 to B14 cells are just imaginary)

Alternatively

=B3+B4+B5+B6.......................so on
Result will be same as before

For instance, we are going to sum up the values of cells F3 to F13
and display the summation at F14.

• Just select the cells F3 to F114.
• Under Home tab click 'Auto Sum'.

Excel will automatically make the sum of cells F3 to F13 and display the resultant figure at F14.

Alternatively
  

• At F14 cell start writing the formula as =sum(
• At this instance just select the F3 to F14 cells altogether.
• Close the bracket.
• Press Enter.

 

Deduct

Say we are going to deduct the cell value F13 from F14 cell value.
Very simple logic.
Write the formula
=F13-F14

Again
We are going to deduct 100 from cell F13 Value.
=F13-100

Multiply

In excel or precisely computer recognize * asterisk as a sign of multiplication.

Say, we want to multiply cell value F13 with G13 and display the result in H13.

At H13 cell write the formula 

=F13*G13

Division

Excel as well as Computer recognizes division with the sign '/'.

Say dividing  F3 cell with F2 cell.

Syntax is 

=F3/F2

Very simple logic.

Auto sum function is always very handy in all instance.

If we observe carefully it combines all function at once.

Observe the ILL 1 below.

ILL1

 

Explanation: 
Auto Sum function combines all.

Go to | last cell | visible cells only

Last Cell

Last cell denotes the cell that contains data and/or formatting.
Even one deletes the data and/or formatting of the cell, Last Cell function identifies that cell. 

Last Cell

Procedure

• Click any where in the sheet.
• Press Ctrl+G and click 'Special..' or Click 'Find & Select' under Home Tab and click 'Go to Special...'.
• Tick the Radio button associated with 'Last Cell' function.
• Click Ok.

Visible Cells Only

'Visible Cells Only' function identifies those cells that are visible in a range. It helps to identify the filtered rows or columns when filters are enabled in a sheet. 

Even it helps to identify any hidden row or column in the sheet.

It is useful.

Conditional Formats

'Conditional Formats' function identifies the cells with conditional format embedded.

Data Validation

Data validation function identifies cells with data validation embedded. Further sub categorized as 'All' and 'Same'.

Go to | Precedents | Dependents

Precedents and Dependents feature sometime proves useful.

Cell Precedents

This function is applicable to cells that contain formulas only.
Precedent cells are cells that contribute 'directly' or 'indirectly/All levels' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cell G1 has two precedent cells namely E1 and F1.
Indirect/All levels precedent cells don't directly contribute to formula result but used as reference. 

Precedent cells are often used to identify irregularities in formula.

How to identify precedent cells?

• Select the cell (to identify precedents)
• Press F2 - This function colorize the precedent cells but is limited to the active working sheet.
• Under Home Tab click Find & Select.
• Click Special.
• Check the radio button associated with 'Precedents'.
• Further check the option 'Direct' or 'All levels'.
• Click Ok.

Shortcut Ctrl+[
This shortcut identify precedent cells in the active sheet.

Cell Dependents

This function is applicable to cells that contain formulas only.
Dependent cells are cells that contribute 'directly' or 'indirectly/All level' to a formula result.
Say cell G1 has a formula =(E1*F1)
Accordingly cells E1 and F1are dependent on G1. There must be at least one dependent cells in case of formula. 

Hence before deleting any formula cell one should go through the precedents and dependents options.

How to identify dependent cell? 

• Select the cell that you want to identify dependency.

• Under Home Tab click Find & Select.
• Click Special.
• Check the radio button associated with 'Dependents'.
• Further check the option 'Direct' or 'All levels'.
• Click Ok.

Shortcut

Ctrl+]

So before deleting any cell one should check the dependency too.

Alternatively

Precedents and Dependents can also be found under 'Formulas' tab.

Under Formula Tab 

ILL 1

Explanations:

G2 & G3 cell contents are the multiplications of E2 and F2, E3 and F3 respectively. 
Active the cell F2 and click 'Trace Dependents'.
Active the cell G3 and click 'Trace Precedents'.
To remove the arrows click 'Remove Arrows' (further sub categorized into 'Remove Dependent Arrows' and 'Remove Precedents Arrows')
 

Go to | Row Differences | Column Differences

Row Differences

Excel has a very important function to find out differences between various rows. The function is called Row Differences.
To access the function 

• Shortcut key - Press Ctrl+G.
• Click Special

Alternatively 

• Under Home Tab click 'Find & Select'.
• Click Go to Special.

 

ILL 1 Row Differences Option

Lets illustrate with a data range.

ILL 2 Data Range

You are requested to go through the above selected data range. Where A2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly row wise. Of course some are common.
After highlighting the data range.

Procedure

• Press Ctrl+G (Instead use Find & Select).
• Click 'Special'.
• Highlight the radio button associated with 'Row Differences'.
• Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations: 
In the 2nd row content of cell C2 is different from cells A2 & B2.
One can argue that the content of cell A3 is different from cell contents of B3 & C3. So why B3 & C3 is highlighted? Why not A3? Since B3 & C3 are similar and A3 is dissimilar. 


Lets observe the data range (ILL 1) once again. 
We can see that the default active cell is A2 (white), so the basis for comparison starts from column A. 

According to the logic since the content of cells B3 & C3 are different from content's of cell A3, hence B3 & C3 has been selected.

The basis can be shifted by pressing Tab key (shifting the active white cell). Result would be different.

Again

Shifting the active cell to cell B3

Result

Explanations: 
Since the basis of comparison is shifted to cell B3, result is different from previous one.Here A3 cell's content is different from B3's content (basis) and C3's content, hence A3 has been highlighted.

Column Differences

Excel has a very important function to find out differences between various rows. The function is called Column Differences.
To access the function 

• Shortcut key - Press Ctrl+G.
• Click Special

Alternatively 

• Under Home Tab click 'Find & Select'.
• Click Go to Special.

ILL3 Column Differences

Lets illustrate with a data range.

ILL4 Data Range

You are requested to go through the above selected data range. Where B2 cell (White cell in the range) is default active. You can observe the various differences of data from one cell to another but mostly column wise. Of course some are common. After highlighting the data range. 


Procedure

• Press Ctrl+G (Instead use Find & Select).
• Click 'Special'.
• Highlight the radio button associated with 'Column Differences'.
• Click Ok.

Cells with different contents are greyed out. For easy understanding highlight the resultant (greyed) cells with Fill color say 'Yellow'.
Result is as follows:

Result

Explanations: 
In case of the column C the contents are different C2 cell's content is different from cell's content of C3 & C4. Hence C3 & C4 has been highlighted.


In case of column differences the basis of comparison is row wise.
If we observe carefully the data range (ILL4) we will see the default active cell in the selected data range is cell B2 and the cell lies in 2nd row. As a result the basis of comparison is the 2nd row.

But if we move the active cell (white cell in ILL4) to 3rd row (by pressing Tab key) and follow the procedure result will be different.

Changed Result

 

Go to | Special | Blank

Microsoft Excel provides a dedicated dialogue box from where you can access some particular group of cells.

• Shortcut Ctrl+G.
• Click 'Special'.

Alternatively

• Under Home Tab click 'Find & Select'.
• Click 'Go to Special'.

Comments

You can find radio button associated with 'Comment' is by default selected when you access the Go to special dialogue box.

Click Ok.

The cells in the range with comments will be greyed out.

Constants

This option refers to cells that contain constants.

There are other sub categories.

• Numbers.
• Texts.
• logical.
• Errors.

Formulas

Formula options refer to cells that contain formulas.

There are other sub categories

• Numbers.
• Texts.
• Logical.
• Errors.

By default all are usually checked.
Say if you want to find only formula that relates to numbers check mark only 'Numbers'.

Blanks

This function denotes those cells that are blank within the data range.

It also recognizes only space/spaces within a cell but the cell is also blank. It will not consider this cell while selection. Since it is not blank technically.

Typically what I believe this function recognizes data column wise.

If the datasheet contains any cell that is non contiguous to other cells but has data in it, it will consider entire column of that cell as blank. Observe the exhibit below.

 

Blank Cells greyed out

The cell marked with Red square has spaces, Blank function didn't consider the cell while selecting blank cells. Also the column J has been considered as it has a single cell of data (Binder). Again the column I has been considered as blank as no data in it but contiguous to main chunk of data. 
But columns K, L, M etc are not considered.

Current Region

Current Region here denotes the entire list or data.

Click any where in the data range and select Current Region, excel will grey out the entire data range. Very effective way to select a large piece of data.

Current Array

It will select entire array (we will discuss array in later posts) if active cell is contained in an array.

Objects

Go to function with 'Objects' selected denotes graphical objects including charts and graphs in the worksheet.

 

Go to | Name Range

Go to lets you move swiftly to a specific line, range, cell, row, column in the workbook.

How to activate 'Go to' window? (Shortcut Ctrl+G)

• Under the Home Tab click 'Find & Select'.
• Click Go to.

Go to window

Instance 1
Selection of Cell D15.

• Under Reference text box type D15. 
• Click Ok.

One thing to remember D15 signifies the cell name as per excel. 
Cursor will move to the intersection of D column and 15th Row.

Instance 2
Selection of Cells A1 to D15.

• Under Reference text box type 'A1:D15'.
• Click Ok.

Go to function can even recognize customized name of cells, range and columns etc.

Instance 3
Selection of non contiguous cells A3 to A4 & C9 to G9 & A11 to G17.

• Under the Reference text box type A3:A4,C9:G9,A11:G17
• Click Ok

Instance 4

Selection of a particular row say 9

• Under the Reference text box type 9:9.
• Click Ok.

Again selection of multiple row say 9,10

• Under the Reference text box type 9:10
• Click Ok.

Instance 5

Selection of a particular column say D

• Under the Reference text box type D:D
• Click Ok

Again selection of columns say D & E.

• Under the Reference text box type D:E
• Click Ok.

Even Go to function can identify any name range which user provides.

• Select the range.
• In the name box type the desired name (advised to ignore digits and put underscore in between). (for instance excel_excel is the desired name selected for the sake of discussion).
• Press Enter.
• You can see in the name box 'excel_excel' has appeared as the customized name of the range previously selected.

Just highlight the desired name from the above box. 
Click Ok.