You are reading the article **How To Use Iserror Function In Excel Vba?** updated in November 2023 on the website Bellydancehcm.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. *Suggested December 2023 How To Use Iserror Function In Excel Vba?*

There are many types of functions in VBA. One of them is Excel VBA IsError. This function can be termed as an informative function because it provides us information as it is a logical function. This function tells us whether a given value contains an error or not. As I said above that it is a logical function means the value or the output generated by this function is either true or false. So how does this function works? We supply the expression to this function and when the expression is turned to be a simple integer or a value in the calculation then the Boolean output by this function is False however if the function returns an error then the Boolean result for this function would be True. True meaning the value or expression does not represent or is not an error while False meaning the value or the expression is an error.

Syntax:

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Iserror (Expression)

How to Use VBA IsError Function?In these examples, we have seen that there are plenty of ways to use the IsError function in VBA. They are as follows,

We can use the IsError function on a range of cells and check whether the cell has an error or not.

We can also change a perfect value to an error and test it with the IsError function.

On the other hand we can also use the expression directly in the code itself.

You can download this VBA IsError Excel Template here – VBA IsError Excel Template

Example #1Let us begin with a basic example, in this example we will take a reference of a cell value from sheet of excel and test whether it contains an error or not. For this, follow the below steps:

Step 2: Once we have a module in our project explorer we can begin with our example, Write the subprocedure of VBA IsError Function.

Code:

Sub

Example1()End Sub

Step 3: Now let us use A2 cell for reference and check whether the value in it is an error or not, currently the cell doesn’t have any value. We will use the Msgbox function to display.

Code:

Sub

Example1() MsgBox IsError(Sheet1.Range("A1")), vbOKOnly, "Does cell A2 contain an error?"End Sub

Example #2Now let us use the concept where we can turn a variable with value to an error. In the following example, we will test two variables one we know for sure is not an error and another variable which we will convert into an error and check with the IsError function. For this, follow the below steps:

Step 1: Let us begin further in the same module from above and can declare another subprocedure.

Code:

Sub

Example2()End Sub

Step 2: Now let us declare two different variables in the procedure.

Code:

Sub

Example2()Dim

var1, var2Dim

isErr1As Boolean

Dim

isErr2As Boolean

End Sub

Step 3: Now let us assign the first variable with a general value and for the second variable we will change the value to an error and then test it with the function known as IsError.

Sub

Example2()Dim

var1, var2Dim

isErr1As Boolean

Dim

isErr2As Boolean

var1 = 10 isErr1 = IsError(var1) var2 =CVErr(11)

isErr2 = IsError(var2) MsgBox isErr2End Sub

We have true as a result because the expression in the function was the second variable and which is an error since this function is an informative function it gave us the result as yes the value in the function is an error.

Example #3In this example, we will notice how to use the IsError function while working with a bunch of code and how we can test it. So in this example, we will see that expression in the code is an error or not. For example, 10/0 is a dividend error but 0/10 is not an error. For this, follow the below steps:

Step 1: So Again we use the same module for our third example and start by declaring a third subprocedure.

Code:

Sub

Example3()End Sub

Step 2: Now let us declare two variables, one as an expression while another as an output for the expression.

Code:

Sub

Example3()Dim

Expression1Dim

OutputAs Boolean

End Sub

Step 3: Now let us put a value to the expression.

Sub

Example3()Dim

Expression1Dim

OutputAs Boolean

Expression1 = 0 / 100End Sub

Step 4: Now we will store the output of the expression in the output variable.

Code:

Sub

Example3()Dim

Expression1Dim

OutputAs Boolean

Expression1 = 0 / 100 Output = IsError(Expression1)End Sub

Step 5: Now we can use the msgbox function to display the result of the output variable.

Code:

Sub

Example3()Dim

Expression1Dim

OutputAs Boolean

Expression1 = 0 / 100 Output = IsError(Expression1) MsgBox "The expression(0/100) is an error or not : " & Output, vbInformation, "VBA IsError Function"End Sub

Step 6: When we run the above code we will get the following result.

Explanation of Excel VBA IsError FunctionsAs we have discussed above that IsError in VBA is an informative function that is used to identify whether a given expression has an error or not. The syntax for the function has one mandatory argument which is the expression that needs to be evaluated.

Also, we can use texts to display in the function to show the relevance as shown in example 3.

Things to RememberThese are the things which we should keep in mind IsError function in VBA and they are as follows,

IsError is an Information function in VBA.

The function has a Boolean value as an output.

The result displayed by the function is either true or false.

Recommended ArticlesThis is a guide to the VBA IsError. Here we discuss how to Use IsError Function in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

You're reading __How To Use Iserror Function In Excel Vba?__

## How To Use Boolean In Excel Vba With Excel Template?

VBA Boolean Operation

In Excel when we compare two cell contents or numbers by applying equal sign between them, we get output in TRUE or FALSE format. Which means values which we are comparing may be equal or may not be equal. In a similar manner, we have Boolean in VBA Excel. Boolean also gives the output in the form of TRUE or FALSE. Boolean is a form of data type which can only contain either TRUE or FALSE. When we give some input to Boolean and if the test becomes TRUE then we get an answer as TRUE or we get FALSE.

How to Use Boolean in VBA Excel?Let’s see the examples of Boolean in Excel VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VBA Boolean Excel Template here – VBA Boolean Excel Template

Example #1 – VBA BooleanLet’s see a very simple example where we will see how to apply Boolean while comparing some.

Step 2: Now in the opened module, write the sub category of VBA Boolean. We can choose to write any name of subprocedure here.

Code:

Sub

VBABoolean1()End Sub

Step 3: Now define a Dim with any name, let’ say an A and assign the variable A as Boolean as shown below.

Sub

VBABoolean1()Dim

AAs Boolean

End Sub

Step 4: Let’s consider two numbers, 1 and 2. And now we will test with the help of Boolean whether 2 is greater than 1 or not. So in the next line, write a mathematical expression of 1<2 under defined variable A.

Code:

Sub

VBABoolean1()Dim

AAs Boolean

A = 1 < 2End Sub

Step 5: Now assign a message box to variable A to see what outcome will appear when we run the code.

Code:

Sub

VBABoolean1()Dim

AAs Boolean

A = 1 < 2 MsgBox AEnd Sub

Step 7: If we change the sign as 1 is greater than 2 as shown below. What would we get?

Code:

Sub

VBABoolean1()Dim

AAs Boolean

MsgBox AEnd Sub

Step 8: To test this, again run the code. We will see, Boolean has given FALSE as 1 cannot be greater than 2.

Example #2 – VBA BooleanIn this example, we will test if Boolean works for text or not. To apply this, we need a module.

Step 1: Open a new Module and give it a subcategory in the name of VBA Boolean or any name as per your choice.

Sub

VBABoolean2()End Sub

Step 2: Define a variable A and assign a Boolean function to it.

Code:

Sub

VBABoolean2()Dim

AAs Boolean

End Sub

Step 3: Now assign a text to defined variable A. Let’s say that text is VBA Boolean. And it should be under inverted commas.

Code:

Sub

VBABoolean2()Dim

AAs Boolean

A = "VBA Boolean"End Sub

Step 4: At last, give that variable A in a message box to see the output as shown below.

Code:

Sub

VBABoolean2()Dim

AAs Boolean

A = "VBA Boolean" MsgBox AEnd Sub

Step 5: Once done, run the code. We will get an error message as “Run-time error 12 – Type Mismatch” which means that Boolean doesn’t support input as Text.

Example #3 – VBA BooleanIn this example, we will see, if Boolean works for a number without any comparison.

Step 1: Open a new module and give it a subcategory of VBA Boolean as shown below.

Code:

Sub

VBABoolean3()End Sub

Step 2: Now define a Dim A variable as Boolean as shown below.

Code:

Sub

VBABoolean3()Dim

AAs Boolean

End Sub

Step 3: As discussed above, we will give the variable A a number. Let’s consider that number is 10.

Code:

Sub

VBABoolean3()Dim

AAs Boolean

A = 10End Sub

Step 4: After that, select the function msgbox and assign it to variable A. This will help us print the value with the help of Boolean.

Code:

Sub

VBABoolean3()Dim

AAs Boolean

A = 10 MsgBox AEnd Sub

Step 5: Now run the code. We will get the message with the message as TRUE.

Step 6: Now let’s change that value to 0 from 10.

Code:

Sub

VBABoolean3()Dim

AAs Boolean

A = 0 MsgBox AEnd Sub

Example #4 – VBA BooleanStep 1: Now, open a new module and write the subcategory of VBA Boolean as shown below.

Code:

Sub

VBABoolean4()End Sub

Step 2: Now define 2 variable with any name as per your choice. Here, we have selected A and B as Integer. Which means both will store numeric values.

Code:

Sub

VBABoolean4()Dim

AAs Integer

Dim

BAs Integer

End Sub

Step 3: Now assign any values to variable A and B. Here we have chosen number 1 and 2 for variable A and B as shown below.

Code:

Sub

VBABoolean4()Dim

AAs Integer

Dim

BAs Integer

A = 1 B = 2End Sub

Step 4: As stated above, we will use the If-Else loop. Now open the If-End If loop where we will write the criteria.

Code:

Sub

VBABoolean4()Dim

AAs Integer

Dim

BAs Integer

A = 1 B = 2 IfEnd If

End Sub

Step 5: Now write the code, If A is less than equal to B then show us the message as TRUE, else show us the message as FALSE.

Code:

Sub

VBABoolean4()Dim

AAs Integer

Dim

BAs Integer

A = 1 B = 2If

A <= BThen

MsgBoxTrue

Else

MsgBoxFalse

End If

End Sub

Step 6: Now compile the above code step-by-step and then run if no error found. We will see, the message box has the message as TRUE which means value stored in variable A (which is 1) is less than the value stored in variable B (which is 2).

Pros of VBA Boolean

It is quite useful when we are want to implement the process flow following TRUE and FALSE for each iteration.

Conditional comparison with the help of any kind of loop can easily be implemented.

Cons of VBA Boolean

Only numbers can be used in Boolean. It will show the error if used for text as seen in example-2.

Things to Remember

Using Boolean with any loop will give users a better output. And comparison can be done in various ways.

Boolean is a kind of cell comparison formula used in excel, but it only compares the numerical or mathematical values.

Always save the file in macro enable format to retain the written code to be used in the future.

Recommended ArticlesThis is a guide to VBA Boolean. Here we discuss how to use Boolean in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

## How To Create Arraylist In Excel Vba With Excel Template?

Excel VBA ArrayList

Data structures are used to store a series of data in programming languages. It binds to the memory rather than address. An ArrayList is one of the data structures in excel. Comparing to normal arrays in excel ArrayList is dynamic. Therefore, no initial declaration of size is needed. ArrayList is not a part of VBA it is associated with an external library which can be used with VBA.

ArrayList can be defined as a list of a nearby memory location. Where the values are retrieved using the index numbers. The list starts from an index number ‘0’, the first element will be inserted into the ‘0’ index and rest is followed by 1, 2, 3, etc. ArrayList offers plenty of built-in operations, sorting, adding, removing, reversing, etc. are some among them.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Adding the LibraryTo use the ArrayList into the VBA it needs to include the library ‘mscorlib.dll’ which comes with .NET framework.

It will lead you to a window with a list of different libraries which supports in VBA and Excel. Scroll down to find the ‘dll’. Tick mark to confirm the selection then press ‘OK’ button.

Now the library is included in your VBA code and it will support different methods associated with an ArrayList.

How to Create VBA ArrayList in Excel?

You can download this VBA ArrayList Excel Template here – VBA ArrayList Excel Template

Excel VBA ArrayList – Example #1How to Add Values to the ArrayList using VBA?

ArrayList act as a list where we can add values. This will automatically store in the different portions starting from 0,1, 2, etc. The values can add or insert to the ArrayList using the add method.

In this example, you will learn how to add a list of values into an ArrayList. Follow the below steps to add ArrayList using VBA Code in excel.

Step 1: To add a list of values to an ArrayList create a function arraylist1.

Code:

Private Sub

arraylist1()End Sub

Step 2: Now we want to include the ArrayList into the function as an object where a list is declared as an ArrayList.

Code:

Private Sub

arraylist1()Dim

alistAs ArrayList

End Sub

Step 3: Since this is an object to use it, you have to create an instance of the ArrayList. Set a new instance for this object.

Code:

Private Sub

arraylist1()Dim

alistAs ArrayList

Set

alist =New

ArrayListEnd Sub

Step 4: Now using the ‘Add’ property of an ArrayList adds the values to the ArrayList. Where the list is added into the index values in an order 0,1,2,3 etc.

Code:

Private Sub

arraylist1()Dim

alistAs ArrayList

Set

alist =New

ArrayList alist.Add "192"'index(0)

alist.Add "168"'index(1)

alist.Add "1"'index(2)

alist.Add "240"'index(3)

End Sub

Step 5: To check whether the values got added into the list, let’s print the array values using a message box. To print the values each index is printed since the values are stored in these partitions.

Code:

Private Sub

arraylist1()Dim

alistAs ArrayList

Set

alist =New

ArrayList alist.Add "192"'index(0)

alist.Add "168"'index(1)

alist.Add "1"'index(2)

alist.Add "240"'index(3)

MsgBox ("\" & alist(0) & "." & alist(1) & "." & alist(2) & "." & alist(3))End Sub

Step 6: Press F5 or run button to run the program and the values will be printed as below. Here an IP address is stored in the ArrayList and while printing the values extra notations are concatenated to form the IP address in a proper format.

Automation error in VBA

It is a common error happens while running an ArrayList. An automation error may encounter ‘Run-time Error ‘-2146232576 (80131700) Automation Error’

Excel VBA ArrayList – Example #2Sorting ArrayList Using VBA Code

ArrayList supports different functions like sorting, reversing, etc. this help to sort the values inserted into an ArrayList. Once you add a list into the ArrayList it is possible to reverse the inserted list.

Follow the below steps to sort the ArrayList using VBA Code:

Step 1: Create a function called arraysort1 to perform the sorting within the inserted values into an ArrayList.

Code:

Sub

arraysort1()End Sub

Step 2: Declare an object ‘arraysort’ of the ArrayList. Use this object to add and sort the values within the ArrayList.

Code:

Sub

arraysort1()Dim

arraysortAs ArrayList

End Sub

Step 3: Similar to the first example need to create a new instance of the declared object. Set this instance as a new ArrayList.

Code:

Sub

arraysort1()Dim

arraysortAs ArrayList

Set

arraysort =New

ArrayListEnd Sub

Step 4: Now using the ‘Add’ method insert the elements to the ArrayList. Which is not possessing any order on values. Randomly inserted some values into the list.

Code:

Sub

arraysort1()Dim

arraysortAs ArrayList

Set

arraysort =New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45"End Sub

Code:

Sub

arraysort1()Dim

arraysortAs ArrayList

Set

arraysort =New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))End Sub

Step 6: Press F5 on the keyboard or run button on the code window to run the program to print the ArrayList. The ArrayList is printed in the same order as it is inserted since we use the index numbers in its correct order.

Step 7: Now to this list apply the sort property of the ArrayList. Use the sort method to sort the inserted list. The sort property will sort the list of values in ascending order by default.

Code:

Sub

arraysort1()Dim

arraysortAs ArrayList

Set

arraysort =New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" arraysort.Sort MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))End Sub

Step 8: Hit F5 or Run button under VBE to run this code, Where the values are sorted and printed in order from smallest value to largest.

Excel VBA ArrayList – Example #3Reversing the ArrayList using VBA Code

When you want to reverse the order of inserted values in an ArrayList reverse method is available. This will reverse the order of the list from its current order. Now we have already sorted the ArrayList in the previous example, which is in ascending order.

Let’s try to reverse the sorted array to make it descending order. Use the reverse method of ArrayList to do this.

Code:

Sub

arraysort2()Dim

arraysortAs ArrayList

Set

arraysort =New

ArrayList arraysort.Add "13" arraysort.Add "21" arraysort.Add "67" arraysort.Add "10" arraysort.Add "12" arraysort.Add "45" arraysort.Sort arraysort.Reverse MsgBox (arraysort(0) & vbCrLf & arraysort(1) _ & vbCrLf & arraysort(2) & vbCrLf & arraysort(3) _ & vbCrLf & arraysort(4) & vbCrLf & arraysort(5))End Sub

After applying the reverse method, the ArrayList will become in descending order and use the message box to print the reversed array. The sorted list is changed from large value to small value.

Things to Remember

ArrayList is dynamic in nature; it does not require re-initialization.

Different built-in methods are associated with ArrayList.

Compared to the array, ArrayList is easy to use in Excel VBA.

The supporting .NET libraries should be included in the VBA to work with ArrayList.

ArrayList is a continuing memory location which identified using index values.

Recommended ArticlesThis is a guide to VBA ArrayList. Here we discuss how to create ArrayList in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

## How To Use Excel Proper Function (Examples + Video)

In Excel. you can quickly change the case of the text in a cell (to lower case, upper case, or proper case) using text functions.

Below is an example of each type of case:

PROPER function is one of the many text functions in Excel.

It takes a string as the input and returns a string where the first letter of all the words has been capitalized and all the remaining characters are in lower case.

Use it when you have a text string and you want to capitalize the first alphabet of each word in the text string and make all the other character in lowercase. This could be the case when you have names in different formats and you want to make it consistent by capitalizing the first alphabet of the first and the last name.

=PROPER(text)

text – the text string in which you want in capitalize the first letter of each word.

Here are some practical examples to show you how to the PROPER function in an Excel worksheet.

Suppose you have the dataset as shown below:

The names in this dataset are all inconsistent.

You can use the PROPER function to make these consistent (where the first alphabet of each name is capitalized and rest all are small).

The below formula would do this:

=PROPER(A2&" "&B2)In the above formula, I use the ampersand operator to add the text in cells in column A and B, and then PROPER function makes the combined string consistent.

Just like the names, you can also use it to make the address consistent.

Below is an example dataset where the addresses are in an inconsistent format:

You can use the below formula to make all these addresses in a consistent format:

=PROPER(A2)Note that this formula works perfectly, but if you want the state code (such CA, NV, NY) in upper case, it will not be done with PROPER function only.

In that case, you need to use the below formula:

=PROPER(LEFT(A2,FIND("@",SUBSTITUTE(A2,",","@",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))),1)))&RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,",","@",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))),1))You can get an idea of how this formula works from this tutorial.

The PROPER function works by analyzing non-text characters in a string. When it finds a non-text character, it capitalizes the next following character. While this works great in most of the cases, in some scenarios, this may not work as expected. For example, if you use the formula on the text – it’s awesome – it will give you the result as It’S Awesome. As it capitalizes the character after the non-text character, it does that with an apostrophe in this case.

Some useful things to know about the PROPER Function:

The PROPER function only affects the first character of every word in a text string. All the other characters are left unchanged.

It capitalizes the first letter of any word that follows a non-text character. For example: =PROPER(hello,excel) returns Hello,Excel

Numbers, special characters, and punctuations are not changed by the PROPER function.

If you use a null character (or a reference to an empty cell), it will return a null character.

Other Useful Excel Functions:

Excel FIND Function: Excel FIND function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is case-sensitive.

Excel LOWER Function: Excel LOWER function can be used when you want to convert all uppercase letter in a text string to lowercase. Numbers, special characters, and punctuations are not changed by it.

Excel UPPER Function: Excel UPPER function can be used when you want to convert all lowercase letter in a text string to uppercase. Numbers, special characters, and punctuations are not changed by it.

Excel REPLACE Function: Excel REPLACE function can be used when you want to replace a part of the text string with another string. It returns a text string where a part of the text has been replaced by the specified string.

Excel SEARCH Function: Excel SEARCH function can be used when you want to locate a text string within another text string and find its position. It returns a number that represents the starting position of the string you are finding in another string. It is NOT case-sensitive.

Excel SUBSTITUTE Function: Excel SUBSTITUTE function can be used when you want to substitute text with new specified text in a string. It returns a text string where an old text has been substituted by the new one.

## Small Function In Excel (Formula, Examples)

SMALL Function in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

SMALL Function in ExcelA small function in Excel is used for getting the smallest number from the selected range of numbers with the help of the Kth position in the range. For example, we have 10 different numbers, and we need to find the smallest number out of that; by using the Small function, we can get the 1st or 2nd or any Kth smallest number out of those 10 selected numbers.

SMALL Formula in Excel:

Below is the SMALL Formula in Excel :

The SMALL function has two arguments, i.e. array, k. Both are required arguments.

Array: This is the range of cells you are selecting it as the source data to find the K value.

K: This is the Kth position of the number. From the list, it gives the bottom value.

In this function, the range should not be empty, and we need to specify both arguments.

How to Use SMALL Function in Excel?This Function in Excel is very simple and easy to use. Let us now see how to use this SMALL Function in Excel with the help of some examples.

You can download this SMALL Function Excel Template here – SMALL Function Excel Template

Example #1Below are the scores of the students on a test. From the below-given data, find the smallest and the 3rd smallest scores.

If we find the smallest number, we can simply apply MIN Function. If you look at the below image, both the formulas return the same value as the smallest number in the given list.

However, MIN Function stops there only. It cannot find the 2nd, 3rd, and 4th smallest numbers. In such cases, SMALL can give us the Kth position number.

Find the 3rd Smallest Number.

We need to specify the number in the Kth argument to find the third smallest score or number from the list.

“In the range, B2:B11 find the 3rd largest value.”

So the result will be :

Example #2Below is the data for a cycle race. From this list, you need to find the winner. Data includes names, start time, and end time.

From this list, we need to find who has taken the least time to complete the race.

Step 1: Find the total time taken.

The time taken to complete the race arrived by deducting the start time by the end time. The image below shows the actual time each one takes to complete the race.

Step 2: Now apply the SMALL Function to get the winner.

So the result will be :

It is a bit of a herculean task if the list is long. But we can just name the winner using the if condition.

So the result will be :

Similarly, it is applied to other cells in that column to get the desired output.

Example #3We can use the SMALL Function along with other functions. From the below-given list, find the sum of the bottom 3 values for Week 2.

Apply the below SMALL function along with the SUM & VLOOKUP function.

This is an array formula. You need to close the formula by typing Ctrl + Shift + Enter. This would insert the curly brackets before and after the formula.

VLOOKUP returns the value for WEEK 2 specified by the SMALL function for 3 bottom values. Then SUM function will add the bottom values together and return the result as 1988.

Things to Remember

SMALL Function ignores text values and considers only numerical values.

Result is :

A SMALL function returns an error if there are no numerical values in the list.

Result is :

If there are any duplicates, then SMALL considers the first value as the smaller one.

K should be numeric; otherwise, it returns the error as #VALUE!

Supplied range should not be empty.

If we find only the least value, we can use the MIN Function. But it finds only the first smallest value.

Even though SMALL ignores text values, if there are any errors, it will return the result as #DIV/0!

We can use SMALL and many other functions to find the Nth values.

Use practically to get the hint of the SMALL function.

If you use SMALL with other functions, it becomes an array formula.

Recommended ArticlesThis has been a guide to SMALL Function in Excel. Here we discuss the SMALL Formula in Excel and how to use a SMALL Function in Excel, with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

## How To Use An Iterating Function In Power Bi

This tutorial will teach you about different iterating functions and how to efficiently use them in your calculations.

I often discuss how calculated columns are not required when making some calculations. This is because of iterators.

Iterators or iterating functions can help you do a calculation without physically putting the results in the table.

This technique can help you save up on the memory needed to load your Power BI data model. In the next sections, I’ll show you how to optimize your calculations using iterators.

To get started, create a new measure for Total Costs. Make sure to select the measure group where you want this new measure to land.

Press Shift and Enter to move down a line before you put the first iterating function, which is SUMX.

In the formula bar of Power BI, you can already see exactly what you need to put after the function as suggested by IntelliSense. For SUMX, you need to add a table after it.

The SUMX formula will run the logic at every single row of the given table. This is why iterators are associated with row context. Within the measure, iterators can turn the formula into a row context.

You will need to reference the Sales table after the SUMX function. To calculate the total costs, you have to multiply Order Quantity by Total Unit Cost.

We don’t need to reference the new column that was created at all. The Total Costs is a measure and I can bring it into my table to evaluate our total costs.

Now, drag the measure inside the table to see the results. Make sure that you selected an initial context from the City filter.

The Total Costs works in a similar way in terms of the initial context. The initial context gets applied to the Sales table, but then within each of these individual results, we’re calculating the Order Quantity multiplied by the Total Unit Cost.

Behind the scenes in our data model, we have turned on our filter and we have context coming in from our Regions table and another context coming in from our Date table. These flow down to our Sales table, which is filtered by the iterating function SUMX.

Since the SUMX function evaluates every single row of the Sales table virtually, there’s no need for a physical column for the results.

After the initial context, SUMX gets the product of Order Quantity and Total Unit Cost for every single row. Lastly, it evaluates all the calculated results from all the rows.

If you noticed, the original Costs column was created through a calculated column. As I’ve said, it’s unnecessary since iterators can already do its work. You can delete it because it can take up unnecessary memory in your model.

Iterating formulas run evaluations at every single row, while aggregating formulas do not.

A lot of this information is covered in-depth in the Mastering DAX course, but this is just to show you the the beginnings of iterating functions and how to start using them when it’s appropriate.

If you feel the need to create a calculated column inside your fact table, I can almost guarantee that an iterating function will do the work for you.

Now, I’ll show you another example of how iterators can do wonders on your calculation. This time, let’s work out the average cost.

Just copy the Total Costs formula and paste it into a new measure. You just have to change the name to Average Costs and then use AVERAGEX instead of SUMX.

The new formula runs a similar logic because it evaluates every single row of the Sales table. Additionally, you still need to get the product of Order Quantity and Total Unit Cost. The only difference here is instead of sum, the formula calculates the average.

Now, if you bring the Average Costs measure to the table, you can see how it compares to the Total Costs measure.

It’s amazing how you can run a similar logic just by changing the iterating function.

To optimize your table, you can delete redundant information like the Total Revenue column.

Since you can readily achieve the average costs, you won’t need the Total Revenue column in your table anymore. As long as you have the Unit Price and the Total Unit Cost columns, everything’s fine.

Now, you can create a new measure instead for Total Sales (Iteration) by using the SUMX function. You just have to reference the Sales table then get the product of Order Quantity and Unit Price.

After that, you can compare the results in the Total Sales and Total Sales (Iteration) columns. They both have the same results, right?

In terms of performance, there’s not much of a difference between using calculated columns and iterators. But when it comes to the data model, an iterator function can get rid of an entire column and save you hundreds of rows of data.

Additionally, you can delete redundant columns because iterators can calculate the necessary results virtually. This practice will make your table a lot thinner and your model a lot faster. Make sure you apply this optimization technique in your own calculations.

To sum up, an iterating function evaluates every single row while aggregators don’t.

The letter X on the end of the function makes it easier to identify iterators. Examples include the SUMX, AVERAGEX, MAXX, MINX functions and more.

Using iterating functions won’t create additional physical tables. This can help you save memory in Power BI.

All the best!

Sam

Update the detailed information about **How To Use Iserror Function In Excel Vba?** on the Bellydancehcm.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!