Calculate average sales per month in Excel
From the sales dataset how to get month sales average in Excel Assume that the sales data is in a table with the following columns: Date, Sales. In a new column, create a formula to calculate the number of days between the date in each row and today’s date. You can use the DATEDIF function […]
How to find duplicates in Customer ID in Excel
There are several ways to find duplicate customer IDs in a dataset using Excel formulas. Here are a few examples: #1: Using the COUNTIF function: You can use the COUNTIF function to count the number of times a customer ID appears in the dataset. If the count is greater than 1, it means that the […]
Using Excel VLOOKUP to find duplicates, merge data, and analyze data
#1: Identifying duplicate customers: You can use VLOOKUP to compare a list of new customers with your existing customer database to identify any duplicates. This can help you avoid creating multiple accounts for the same customer and ensure that you have accurate and up-to-date information for all of your customers. Here’s an example of how you […]
Common Power Query data transformations using M-Query
M is the programming language used in Power Query to perform data transformations. Here are some examples of M code that you might find useful for data transformation. Table.SelectRows: This function allows you to select rows from a table based on a condition. For example, you could use Table.SelectRows(#”MyTable”, each [Column1] > 100) to select […]
5 Dynamic Array Formulas That Replace Old Excel Formulas
Following are the 5 Dynamic Array formulas which replace old Excel formulas: FILTER: This function can replace the IF, AND, and OR formulas, which are used to filter data based on multiple criteria. FILTER allows you to specify multiple criteria and returns an array of values that meet all of the criteria, whereas IF, AND, and […]
