How Do You Add Columns In Excel
What is the Cavalcade Role in Excel?
The COLUMN function in Excel is a Lookup/Reference function. This office is useful for looking up and providing the column number of a given cell reference. For example, the formula =Column(A10) returns ane, because column A is the first column.
Formula
=COLUMN([reference])
The Cavalcade office uses only i statement – reference – which is an optional statement. It is the jail cell or a range of cells for which nosotros want the column number. The function volition give us a numerical value.
A few points to remember for the reference argument:
- Reference can exist a single jail cell accost or a range of cells.
- If not provided by us, and so it volition default to the prison cell in which the column part exists.
- We cannot include multiple references or addresses as reference for this part
How to employ the COLUMN Function in Excel?
It is a built-in office that can exist used every bit a worksheet function in Excel. To sympathize the uses of the COLUMN Function in Excel, permit's consider a few examples:
Example one
Suppose nosotros are given the following references:
In the offset reference that we've provided, we did not give a reference then it gave united states of america the result 4 with the COLUMN role in prison cell C5, as shown in the screenshot beneath:
When we used the reference B6, it returned the issue of 3 every bit the cavalcade reference was B.
Every bit you tin can meet above, when we provide an array, the formula returned a result of 3.
Instance 2
Suppose a flower expense is payable every 3rd month. The bloom expense is $100. If we wish to generate a fixed value every 3rd calendar month, we can employ a formula based on the Modern office.
The formula used is =IF(Modern(Cavalcade(B8)-one,iii)=0,$A$2,0), as shown below:
And so, in the instance higher up, we took a recurring expense at every 3 months. If nosotros wish to get information technology one time in ii months, and so nosotros will change the formula to:
=IF(MOD(Column(B19)-i,2)=0,$A$4,0)
The formula can as well be used for a fixed expense every regular time interval, a stock-still payment every 6 months such as premiums, etc.
Here, ii formulas are used: MOD and COLUMN. Modern is the chief function used. The function is useful for formulas that are required to do a specified act every nth fourth dimension. Here, in our example, the number was created with the Column function, which returned the column number of cell B8, the number 2, minus i, which is supplied as an "offset." Why did we utilize an showtime? It is because nosotros wanted to brand sure that Excel starts counting at ane, regardless of the actual column number.
The divisor is stock-still as 3 (blossom expense) or ii, since we want to do something every 3rd or second month. By testing for a zilch remainder, the expression will return TRUE at the threerd , 6th , nineth , and 12th months or at the 2nd, 4th, 6th, 8thursday, 10th and 12th months.
Using the formula and working with a date
As shown in the screenshot below, we tin utilise a date instead of using a text value every bit we've used for months. This will assistance united states in building a more rational formula. All we need to do is accommodate the formula to extract the calendar month number and apply that instead of the column number:
=IF(Modernistic(MONTH(A19),three)=0,$A$four,0)
Example 3
If we desire, we can use this column part to become the sum of every nth cavalcade. Suppose we are given the following information:
As we wish to sum every nth cavalcade, we used a formula combing three Excel functions: SUMPRODUCT, MOD, and Cavalcade.
The formula used is:
=SUMPRODUCT(–(Mod(COLUMN(B4:F4)-Column(B4)+1,G4)=0),B4:F4)
Allow united states see how the Column Office in Excel works.
In the formula above, Cavalcade One thousand is the value of n in each row. Using the MOD part will return the remainder for each column number later on dividing information technology by N. So, for example, when N = 3, Mod volition return something similar this:
{one,2,0,1,2,0,1,two,0}
So, the formula uses =0 to get True when the remainder is zero and Simulated when information technology is not. Note that we used a double-negative (–) to go True or Fake to ones and zeros. It gives an array similar this:
{0,0,1,0,0,1,0,0,1}
Where 1s now indicate "nth values." It goes into SUMPRODUCT as array1, along with B5:F5 every bit array2. At present SUMPRODUCT multiplies and then adds the products of the arrays.
And so, the only values of that last multiplication are those where array1 contains ane. In this way, we tin call up of the logic of array1 "filtering" the values in array2.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI's guide to important Excel functions! By taking the time to acquire and chief these functions, you'll significantly speed up your financial assay. To learn more, check out these additional CFI resource:
- Excel Functions for Finance
- Avant-garde Excel Formulas Course
- Advanced Excel Formulas You Must Know
- Excel Shortcuts for PC and Mac
Source: https://corporatefinanceinstitute.com/resources/excel/functions/column-function-in-excel/
Posted by: bookercantences88.blogspot.com
0 Response to "How Do You Add Columns In Excel"
Post a Comment