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 delete quick recipe on Excelin addition to locking excel formulas, we can delete formulas and retain results, or delete all cells with formulas and results.
- Excel tricks that let you write formulas more easilyif you've ever stared at a long excel formula and thought, 'is there a better way to write it?', the good news is that there is. use lambda and let.
- How to lock formulas in Excel - Protect excel formulaswhen sharing excel files, if you don't want others to edit the formulas or view the formulas, you can lock the formulas.
- Array (Array) in C #an array stores a set of fixed-size elements in the same type. an array is used to store a data set, but it is often more useful to think of an array as a set of variables of the same type stored in adjacent memory locations.
- Complete guide to Excel 2016 (Part 13): Introduction to formulasafter learning about the page format and spreadsheet printing, please join tipsmake.com to learn about the introduction article in excel 2016!
- MS Excel 2003 - Lesson 5: Excel formulas and functionsformulas in excel are programs that perform calculations on data tables. these formulas perform very precise calculations such as addition, multiplication, or comparison of values in a worksheet.
- Array (Array) in C / C ++c / c ++ programming language provides data structures called arrays, stored in a set of data of the same type with fixed length. an array is used to store data sets, but it is useful if you think of an array of variables with the same type.
- How to Copy Formulas in Excelthe excel program has a feature that makes it easy for users to copy formulas for entire rows or columns, but you don't always get the results you want. when you receive results that are not as expected or encounter #ref and /div0 errors, you will definitely feel unhappy. don't worry, though - you don't need to edit every cell in a 5,000-line spreadsheet. this article will provide some simple ways to help you copy formulas to other cells.
- How to lock formulas in Excelwhen sharing an excel file, you don't want other people to edit the formulas, see the formulas, then lock the formulas again.
- Array (Array) in JavaScriptarray object - array helps you store multiple values in a single variable. it stores a set of fixed-size ranges of elements in the same type (type). an array is used to store a data set, but it is often more useful to think of an array as a collection of variables in the same type.