# Trending December 2023 # How To Use Excel Proper Function (Examples + Video) # Suggested January 2024 # Top 13 Popular

You are reading the article How To Use Excel Proper Function (Examples + Video) updated in December 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 January 2024 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.

You're reading How To Use Excel Proper Function (Examples + Video)

## Small Function In Excel (Formula, Examples)

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

SMALL Function in Excel

A 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 #1

Below 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 #2

Below 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 #3

We 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 Articles

This 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 Iserror Function In Excel Vba?

Excel VBA IsError

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 #1

Let 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 #2

Now 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, var2

Dim

isErr1

As Boolean

Dim

isErr2

As 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, var2

Dim

isErr1

As Boolean

Dim

isErr2

As Boolean

var1 = 10 isErr1 = IsError(var1) var2 =

CVErr(11)

isErr2 = IsError(var2) MsgBox isErr2

End 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 #3

In 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

Expression1

Dim

Output

As Boolean

End Sub

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

Sub

Example3()

Dim

Expression1

Dim

Output

As Boolean

Expression1 = 0 / 100

End Sub

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

Code:

Sub

Example3()

Dim

Expression1

Dim

Output

As 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

Expression1

Dim

Output

As 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 Functions

As 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 Remember

These 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 Articles

This 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 –

## How To Use Google Sheets Sumif Function

The SUMIF function in Google Sheets is used to sum across a range of cells based on a conditional test. The SUMIF function only adds values to the total when the condition is met.

Let’s see an example. Suppose we want to calculate the total order value for John only:

The SUMIF formula that calculates the total order value for John is:

=SUMIF(

A2:A11

,

"John"

,

C2:C11

)

which gives an answer of \$2,059.

The formula tests column A for the value “John”, and, if it matches John, adds the value from column C to the total. I’ve highlighted the four rows in yellow that are included.

🔗 Get this example and others in the template at the bottom of this article.

SUMIF Function Syntax

=SUMIF(range, criterion, [sum_range])

It has two mandatory arguments and one optional argument.

range

The range is the data that you want to test with your criterion. If the third argument (sum_ragne) is omitted then this range will also be used for the sum.

criterion

The criterion is the test you want to apply. If you want to apply multiple tests to your sum, then you need to use the SUMIFS function.

[sum_range]

This is an optional argument that lets you specify a different range to use to calculate the sum total. It must be the same size as the test range.

SUMIF Function Notes

The following dataset (available in the template below) is used in all the examples that follow:

Text Criterion

Text criterion must be enclosed by quotes.

This formula calculates the total revenue for Germany:

=SUMIF(

B2:B21

,

"Germany"

,

G2:G21

)

Numeric Criterion

Exact matches with numeric values do not require quotes but logical tests do require quotes.

For example, this formula calculates the total revenue from orders that have exactly 10 items as the quantity:

=SUMIF(

F2:F21

,

10

,

G2:G21

)

However, if the criterion includes a logical test, then the formula requires double-quotes.

Notice how this last SUMIF formula does not include the optional sum range. In this case, the conditional range to test is also the range used for the sum.

Conditional Tests

Logical operators are used to create conditional tests:

= equal to

not equal to

They must be enclosed with double-quotes.

This formula calculates the total revenue of orders with more than 5 items:

Case Insensitive

The SUMIF function is case insensitive, so “john”, “JOHN”, or “John” will all produce identical results.

SUMIF Function with Blanks and Non Blanks

To sum blank cells in a range, use empty double-quotes:

=SUMIF(

E2:E21

,

""

,

G2:G21

)

To sum non-blank cells in a range, use the not-equal logical operator ““:

=SUMIF(

E2:E21

,

""

,

G2:G21

)

Reference another cell

The test criterion for SUMIF function can be contained in a different cell (in this example G26) and referenced by the SUMIF formula:

=SUMIF(

A2:A21

,

G26

,

G2:G21

)

If you want to use a logical operator, then it must still be enclosed in double-quotes. Use an ampersand to combine with the reference cell, e.g.

Using Wildcards with SUMIF

SUMIF Google Sheets supports three wildcards, *, ?, and ~.

The star * matches zero or more characters.

The question mark ? matches exactly one character.

The tilde ~ is an escape character that lets you search for a * or ?, instead of using them as wildcards.

Let’s see some examples:

Star Example

What’s the revenue from “Pro” products?

This formula finds everything containing “Pro”:

=SUMIF(

C2:C21

,

"*Pro*"

,

G2:G21

)

Question Mark Example

What’s the revenue from customers 10, 11, and 12 combined?

This can be achieved in two ways. Firstly, using a SUMIF for each of the customers and adding them together, like so:

=SUMIF(

E2:E21

,

"Customer 10"

,

G2:G21

) + SUMIF(

E2:E21

,

"Customer 11"

,

G2:G21

) + SUMIF(

E2:E21

,

"Customer 12"

,

G2:G21

)

Or, you can use a question mark as a placeholder to catch 10, 11, and 12 in one go, like this:

=SUMIF(

E2:E21

,

"Customer 1?"

,

G2:G21

)

The question mark matches a single character, so only Customers 10, 11, and 12 will match. This is obviously specific to this dataset but illustrates how the question mark works.

Both of these formulas give the same answer of \$43,700.

Tilde Example

The tilde escape character lets you search for a * or ? without the special meaning above being applied.

For example, to calculate the revenue of Office Pro products with a * next to their name in the table above, we use:

=SUMIF(

C2:C21

,

"Office Pro ~*"

,

G2:G21

)

SUMIF Function Template

The SUMIF function is also covered in Day 2 of my free Advanced Formulas 30 Day Challenge course.

For conditional counting, have a look at the COUNTIF function and COUNTIFS function. They work in the same way as SUMIF but count the values that match the criterion.

## 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 Boolean

Let’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

A

As 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

A

As Boolean

A = 1 < 2

End 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

A

As Boolean

A = 1 < 2 MsgBox A

End 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

A

As Boolean

MsgBox A

End 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 Boolean

In 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

A

As 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

A

As 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

A

As Boolean

A = "VBA Boolean" MsgBox A

End 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 Boolean

In 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

A

As 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

A

As Boolean

A = 10

End 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

A

As Boolean

A = 10 MsgBox A

End 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

A

As Boolean

A = 0 MsgBox A

End Sub

Example #4 – VBA Boolean

Step 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

A

As Integer

Dim

B

As 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

A

As Integer

Dim

B

As Integer

A = 1 B = 2

End 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

A

As Integer

Dim

B

As Integer

A = 1 B = 2 If

End 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

A

As Integer

Dim

B

As Integer

A = 1 B = 2

If

A <= B

Then

MsgBox

True

Else

MsgBox

False

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 Articles

This 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 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 Excel Proper Function (Examples + Video) 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!