Instructions and examples of array formulas Array Formulas in Excel
The following article details array formulas and practical examples to help you better understand and apply them in your work most effectively. To use the array formula you always remember when you enter the calculation formula, press Ctrl + Shift + Enter instead of the Enter key .
1. Array formula to sum by condition, count by condition
For conditional sums we often use the sumif function but to simplify the formula try tracking the example of conditional sum using array formulas.
For example: Calculate the total number of salespeople with a number greater than 38. And calculate the total number of sales of those employees.
There are tables of data:
1.1. Counts the number of employees that sell more than 30 products
In this case, you can use the function counif but here I introduce how to use the Sum array formula. In the cell you need to calculate the following formula: = SUM (B4: B10> 30) .
Press Ctrl + Shift + Enter . Result:
Because the comparison condition returns True or False and uses the Sum function, here you add the following formula:
Press F9 -> Press Ctrl + Shift + Enter to get the results as shown below:
1.2. Conditional sum using array formulas
Calculates the total number of products sold by people who have sold more than 30 products.
Step 1: In the cell to calculate enter the formula.
Press Ctrl + Shift + Enter to get the results:
For some devices, if you enter the above formula without giving the correct results, highlight the parameter in the Sum function -> Press F9 to check the results again -> press Ctrl + Shift + Enter .
2. The array formula used in calculating the maximum difference of stock codes over days
For example: Calculating the maximum and minimum price difference of a stock code by day:
Normally you will create an extra column to find the difference then use the Min and Max functions. In this article, we will use array formulas to calculate without creating an extra column of difference prices.
Step 1: At the cell that needs to calculate the difference, enter the following formula = Max (E5: E10-D5: D10).
After entering the formula, press Ctrl + Shift + Enter to get the results. You can check by highlighting the parameters in the Max function and pressing F9 to check:
Here Max will value in an array of 6 elements.
3. Calculate Max, Min according to conditions combining array formulas
The example here calculates the largest and smallest sales of employees sold during the month. In the cell to calculate the largest sales enter the formula: = Max (If ($ B $ 5: $ B $ 10 = E6, C5: C10)) .
After entering the formula, press Ctrl + Shift + Enter to get the result:
Calculating the smallest sales is the same as the largest sales that is only replaced by the MIN function :
Similarly, you calculate for the remaining employees only to change the If condition that is compared to that employee code.
4. Find items that are delivered to agents in the North, Central and South
Using the Index function, match in combination with an array formula.
In the cell to calculate enter the following formula: = IFERROR (INDEX ($ D $ 5: $ D $ 12, MATCH ($ F $ 6 & $ G $ 5, $ B $ 5: $ B $ 12 & $ C $ 5: $ C $ 12,0) ), "") .
After entering the formula, press Ctrl + Shift + Enter to get the results:
Similarly dragging the formula to the remaining cells results:
Here, use the IFERROR function to return an error-free result when in the North, the Middle of the Fruit Paring Machine does not have an empty value.
5. Calculate the total cost
For example: Calculating the total cost of items SP1, SP2, SP3 based on the cost table and the number of sp code appearances in the product table sold.
In the cell to calculate enter the formula: SUM (SUMIFS (H5: H7, G5: G7, C5: C9)) .
After entering the formula, press Ctrl + Shift + Enter to get the result:
6. Calculate the total sales of the employees who sold the most
Here you can choose the sum of the n most sold employees with n = 2, 3, 4 .
For example, calculate the total sales of the 3 employees who sell the most.
In the cell to calculate, enter the formula: = SUM (LARGE (C6: C12, {1,2,3})) where {1,2,3} is an array containing the number of elements to sum, here is 3.
After entering the formula, press Ctrl + Shift + Enter to get the result:
For example: Calculate the total sales of the 4 employees with the largest sales by simply adding the 4 element to the array:
7. Use an array formula to number the order to ensure the data components are not deleted
Step 1: Highlight the data range you want to create ordinal numbers -> and above on entering the formula.
After entering the formula, press Ctrl + Shift + Enter to get the result:
By entering the ordinal number from the array formula to change the composition, the order of the column No. is not changed. For example, one more line: Boi black any line -> Right-click and choose Insert .
The row numbers 1, 2, 3, 4 and 5 are changed but the ordinal numbers in the ordinal column are not changed.
The above are some examples of how to use array formulas depending on your work of choice.
Good luck!
You should read it
- Array (Array) in C #
- Instructions on how to copy formulas in Excel
- Array (Array) in C / C ++
- MS Excel - Lesson 5: Excel formulas and functions
- Array (Array) in JavaScript
- Guidelines for importing Chemistry formulas in Excel
- Introduction to 2D Array - 2-dimensional array in JavaScript
- How to create an Excel formula in Notepad ++
May be interested
- How to create a frequency chart in Excelhow to create a frequency chart in excel. with the frequency chart you can determine the frequency of occurrence of the event, the variability ... the following article gives detailed instructions on how to create frequency charts in excel.
- Insert images, logos, text in Excel 2010, 2013, 2016in the process of creating an excel file, you want to create your own personal identity or copyright, you can create text or logo sink. the following article details how to insert text, logo sunk in excel.
- Add trend lines, moving averages to charts in Exceladd trend lines, moving averages to charts in excel. to make the conjecture easy and high accuracy, people often use trendlines to predict future trends.
- Excel compatible functionthe following article introduces in detail the functions that are in the compatible function group in excel.
- Intrate function - Calculate interest rates in Excelthe following article details how to use the intrate function to calculate the interest rate of invested securities.
- MIRR function - The function returns the adjusted internal rate of return in Excelsometimes in the process of investing in securities you wonder and do not know when you can payback or how to calculate whether the next investment is profitable or not. the following article shows how to use the mirr function to help you answer the above question.