Array Formula in Excel
The difference between array formulas and regular formulas is that an array formula processes several values instead of just one. In layman’s words, an array formula in excel evaluates all individual values in an array and performs multiple calculations on several items. Not only can an array formulas deal with several values simultaneously, it can also return values at a time. So, the result returned by an array formula is also an array.
Example 1: Addition
You have some items in column A, and other items in column B, column C. column D and if you want to know the total expenditure add all these.
You can do this simple addition by entering (=A2+B2+C2 +D2) in the formula bar, and then you will get the final result.
Once you do this, Microsoft excel surrounds the formula with {curly braces}, which is a visual indicator of an array formula. what the formula does is add the value in each individual row of the specified array (cell A2 through D6).
This simple example shows how powerful an array formula can be. When working with hundreds and thousands of rows of data, just think how many times you can save by entering one array formula in a single cell.
Example 2: Multiplication
You have a huge number to multiply how you can do that by just enter “=” sign and then select the cell you want to multiply.
You can do this simply by entering (=A2*B2*C2*D2)
Creating a Simple Interest Calculator in Excel
To get the interest just insert =(principal*rate of interest*time)/100 into interest
You have principal in column A, rate of interest in column B, and time in column C and you want to know interest. You can do this simple addition by entering ( =(A2*B2*C2)/100 ) in the formula bar, and then you will get the final result.
If you want to know the total amount just enter (=D2+A2) into the formula bar and you will get the result.
How to Create an Array Formula in Excel?
Array formulas in excel are important tools. A single formula can perform multiple calculations and replace thousand of useful formulas. Most of us have never used array functions in our excel sheet just because we don’t know how to use these formulas.
Array formulas is one of the most confusing excel features to learn. The aim of this tutorial is to make you learn all functions in a smooth way. In this article, we will learn about array, array formulas, how to enter array formulas into your excel sheet, and how to use array constant and array functions.
Contact Us