Excel Formula

How To Sum a set of Cells Ignoring #N/A Errors In Excel || 3 tricks for the problem|| DataWitzz

How To Sum a set of Cells Ignoring #N/A Errors In Excel

We often encounter an issue where we wanted to sum set cells which already have #N/A in one or more cells. Due to these N/A values sum also reflects N/A instead of a number. How to sum a set of cells ignoring #N/A errors in Excel?

How To Quickly Sum a set of Cells Ignoring #N/A Errors In Excel?
How To Quickly Sum a set of Cells Ignoring #N/A Errors In Excel?

We can see in the above example that there were two entries of #N/A in the dataset due to which #N/A was reflected as a sum value. To encounter these types of problems we would be discussing a few ways forward in this tutorial.

SUMIF function in excel can … Continue Reading

5 Excel functions for average: 5 Useful functions in excel to calculate average || DataWitzz

5 Excel functions for average

We often require to find the average score of data sets in our regular excel use. Even sometimes we need to find the average basis of some criteria. Excel provides multiple functions to calculate the average. We will be discussing the following average function in this article.

  • AVEDEV
  • AVERAGE
  • AVERAGEA
  • AVERAGEIF
  • AVERAGEIFS

Let’s understand all of them one by one

AVEDEV is used to get the return of the average of the absolute deviations of data points from their mean. AVEDEV is basically a measure of the variability in a data set. Syntax for AVEDEV will be as given below.

5 Excel functions for average
5 Excel functions for average

LLets understand this function from the below example as illustrated … Continue Reading

Difference between absolute and relative reference : 3 Types of cell references

Difference between absolute and relative reference

In normal excel use, we are facing issues with cell values when we are copying a cell from one place to another place. It happens due to references used in the cell. Usually, there are two cell references in excel 1) absolute reference and 2) relative reference. We also use mixed references. Let’s understand it in detail.

Relative References

BY default every cell reference is the relative reference. When copied across cells the values also change relatively.

Difference between absolute and relative reference in Excel
Relative Reference in excel

In the above example, we can see that we first refer to value in CELL B3 and when we copied it to cell E3 the cell reference got changed from cell … Continue Reading

How to import text file to Excel and Automate it without VBA: How to use Get and Transform option in excel to import data|| DataWitzz

How to import text file to Excel and Automate it

Importing a text or CSV file in excel is always been a tidy task for data analysts. With the help of Get and Transform data this task has become very easy. This option not only helps in importing data but also helps in transforming the data as per our requirement, like removing a particular column or using some filter before importing or changing the data type of the column becomes very easy. Let’s understand this by a real-time example.

To import excel we will go to Data tab then Get and Transform option. After clicking Get and Transform we will get the different options for importing the data. We will … Continue Reading

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

How to use the IF function in excel ||Learn it with 2 examples||DataWitzz

How to use the IF function in excel

IF function in excel is used to evaluate a given condition if it is TRUE or FALSE and returns a value whether a condition is TRUE or FALSE. It is a conditional function in excel and it helps in testing a value based on the condition. IF function in excel works with other logical functions like AND,OR etc. It is also being use as nested function to incorporate multiple condition.

Let’s understand the syntax of IF function from the below image

How to use the IF function in excel
Syntax of IF function

In the above image logical_test means the condition that we wanted to evaluate, [value_if_true] it is the value that comes when condition is True and [value_if_false] is … Continue Reading

How to combine text in excel by using the TEXTJOIN function

How to combine text in excel by using the TEXTJOIN function

Combining text in excel from different cells can be done in different ways. We can use ‘&’ to combine or can use CONCATENATE or CONCAT function to combine texts from different cells. But in all these methods when we are using some delimiters we need to put delimiter multiple times with every text also there was no provision of treating blank cells.

How to combine text in excel
How to combine text in excel

In the above example, we can two different examples of concatenation. One without any empty cell and the second with an empty cell. In the empty cell, we can see delimiters got concatenated when a cell has no value. Also, we … Continue Reading

How to sort your data in excel: 4 ways of sorting the data in excel

How to sort your data in excel

Microsoft Excel provides multiple options to arrange our data in excel. We can sort our text data alphabetically, dates chronologically, numbers smallest to highest or highest to smallest, and even we can customize our sort logic. In Excel 365 Microsoft provides two formulas as well to sort it dynamically. We will concentrate our discussion on the conventional method of sorting.

To use the sort option in excel we will go to Data tab Sort as illustrated in the below image. After clicking OK we will get a new dialog box.

How to sort your data in excel
How to sort your data in excel

In the above image, we can see different options like Add Level by which we can … Continue Reading