Excel Array Formulas
How to implement array formulas in Excel
- Enter your data and organize it.
- Next select appropriate ranges and name them as shown in the training video. Named ranges make the use of array formulas easier.
- Enter your formuala.
- Now press 'CTRL'+'SHIFT'+'ENTER' keys simultaneously to display the result of the formula.
- In case you forget to press the keys and press only enter, you will see the '#value' error.
- You can use functions like sum, average, max, min, etc. in your array formulas.
Some knowledge about how the array formulas work in the background
Place your cursor in cell E12 where the value '66' is displayed. Press 'F2' to view the formula. In the formula bar above now select 'product="Shirts" ' and press 'F9'. You'll notice 'FALSE' and 'TRUE' displayed. Wherever the value 'shirts' is available, 'TRUE' is displayed otherwise 'FALSE' is displayed.
Now select 'quantity' and press 'F9' on the keboard to display the 'quantity' of shirts.
Only where the 'shirts' is 'TRUE' the 'quantity' values are added and you get the result '66'.
In this last image below you select both 'product="Shirts" ' and 'quantity' and press 'F9' to see all the background data.
