Function

How to calculate EMI in excel with Principle and the Interest components | DataWitzz

How to calculate EMI in excel

How to calculate EMI is most annoying thing when we take loan or planning to take loan. It also very difficult when we wanted to check interest and loan amount components spread over EMI tenure. Though there multiple online stuffs are available to calculate EMI but the spread over EMI tenure is very early. I just tried to make it excel which will surely help you.

Let us first learn How to calculate EMI in excel :

In excel to calculate EMI we would use PMT function of excel. Syntax for PMT function is as below:

How to calculate EMI in excel
How to calculate EMI in excel

PMT function requires below input to calculate EMI:

  • rate: interest rate
Continue Reading

Common Excel errors and Tips to handle them: 5 most common excel errors

Common Excel errors

While working with excel we usually encounter some errors as an output. These common excel errors can impact calculations of the workbook. We will be discussing these Common Excel errors and Tips to handle them in the below article. Hope this will help you understand these error and solutions to handle these.

  1. Value Error (#VALUE!) : These errors reflect when we put a wrong variable value in the formula. Like when adding five values out of which four are numeric and one is aplhabet, then in that case this error will reflect. Let us see below example to understand this.
Common Excel errors
Common Excel errors

In the above example, we can see that while adding numeric values … Continue Reading

How to use logical functions in excel: AND, OR, NOT, and XOR || DataWitzz

How to use logical functions in excel

There are basically 5 types of logical functions available in excel which are mostly used in day-to-day life. These functions are as mentioned below:

  • IF(S)
  • AND
  • OR
  • NOT
  • XOR

You can read about the IF function from the link Here. I will be explaining the use of the rest four functions.

AND function is used to evaluate a condition where we wanted to check if all conditions are true or not. It will throw true or false based on all conditions matched or not. Let us see the syntax of AND function in excel

How to use logical functions in excel
Syntax of AND function

The syntax is very simple just use conditions in logical values. Let us understand … Continue Reading

dependent data validation list excel

How to create multiple level dropdown list in excel

How to create multiple level dropdown list in excel

Data validation in excel is one of the useful methods to put validation in cells of excel. In this article, we will learn how to create multiple level dropdown list. Multiple level dropdown list also known as dynamic data validation or multiple level data validations. It helps in putting validation in the second cell based on the selection of the first cell. By using this if we select a particular value in the first cell then the second cell will reflect values related to the first cell.

Let us start with an example of how to create multiple dependent drop down list in excel. We have a data set with two … Continue Reading

How to use INDIRECT function in Excel

How to use INDIRECT function in Excel

In this blog post, we will try to understand about INDIRECT function of excel. The excel INDIRECT function returns a reference to a range. We can use this function to create a reference that won’t change if rows or columns are inserted in the worksheet. Or, cause it to create a reference from letters and numbers in other cells.
The INDIRECT function in excel has two arguments as seen in the below picture:

How to use INDIRECT function in Excel
Indirect function in excel
  1. ref_text: A cell reference or text string (or both), that creates the range reference. The referenced range can be a cell, a range of cells, or a named range.
  2. a1: TRUE or FALSE. Does the
Continue Reading

How to use MATCH function in Excel

How to use MATCH function in Excel

In this blog, we will be discussing MATCH function in excel. MATCH function basically helps in finding the position of a value. It will not fetch any number but will provide only the position of the lookup value in excel. This function is very helpful when it works with other functions like OFFSET/INDEX.

Let us understand the anatomy of excel MATCH function. It requires 3 input values as can be seen in the below picture.

How to use MATCH function in Excel
match function excel
How to use MATCH function in Excel
How to use MATCH function in Excel

match_type can be explained as below:

match function option
Match function option

In example 1: we will try to find out the position of USA in the country … Continue Reading

How to use HLOOKUP in Excel

How to use HLOOKUP in Excel

HLOOKUP is one of the functions provided by Microsoft Excel which helps us find a value for a particular reference value. Its functions are the same as VLOOKUP, just the difference exists in terms of fetching data horizontally instead of vertically.

This formula also requires 4 inputs as mentioned below:

HLookup1
How to use HLOOKUP in Excel 41
How to use HLOOKUP in Excel
How to use HLOOKUP in Excel 42

We will take an example to understand this in a better way. As we see in the below table we have data for student’s marks achieved in different subject. Now if I wanted to fetch data for a particular student for a particular subject then we will use HLOOKUP to … Continue Reading

How to use VLOOKUP function in excel | VLOOKUP formula in excel with example

How to use VLOOKUP function in excel

Excel Vlookup is the most sought formula in excel and is widely used by everyone. It is used basically for looking up values vertically means from the column.

Before learning how to use vlookup excel we need to understand about different inputs of excel vlookup formula. As we can see below picture there are 4 different inputs required for using excel vlookup function.

How to use VLOOKUP in excel
Vlookup formula

The explanation of values are given as given below:

How to use VLOOKUP in excel
How to use VLOOKUP in excel

Let us understand this by and example.

In the below table we can see the name of the person and sales done by them on the month on month basis. Now we wanted … Continue Reading

Different COUNT functions in Excel: How to use these functions

Different COUNT functions in Excel

Excel COUNT function is a very important function in excel and it is being used very frequently. There are five COUNT functions in excel(count, counta, countblank, countif,countifs) that are being used in excel. I will explain every function one by one how to use the count function in excel and have also shared a video with a full explanation.

Count: This function counts the number of numeric entries in a list. It will ignore blanks, text, and errors.Syntax: =COUNT(Range1,Range2,Range3……………….,RangeN)

Different COUNT function in Excel
Different COUNT functions in Excel

Counta: This function counts the number of numeric and text entries in a list. It will ignore blanks.Syntax: =COUNTA(Range1,Range2,Range3……………….,RangeN)

counta function in excel
counta function in excel

Countblank: This function counts the number of … Continue Reading