Most of the people who use computers, have in some way or form used excel. Its always handy to know few important excel formulas that can help us write 1-liner code that otherwise in normal programming languages like C, C++ or even C# would have taken 10 or more lines of code. In this blog post, I will keep a list of helpful excel formulas.
If
offset - returns a reference to a range which is offset from provided cell/range
match - searches for a value in an array and returns the relative position
If
syntax: if(condition, true value, false value)
example: =if(A3>B3,"A3 is larger", "B3 is larger")
SumIf
syntax: sumif(range, condition, sumrange)
example: =sumif(C5:C14, C17, D5:D14)
In this example, sumif will first search in the range C5:C14 to see which of these cells are equal to C17, then for these cells it will sum up the corresponding values in the range D5:D14.
SumIf
syntax: sumif(range, condition, sumrange)
example: =sumif(C5:C14, C17, D5:D14)
In this example, sumif will first search in the range C5:C14 to see which of these cells are equal to C17, then for these cells it will sum up the corresponding values in the range D5:D14.
offset - returns a reference to a range which is offset from provided cell/range
syntax: offset(from cell/range, rows up/down, column left/right, num of rows, num of cols)
example: =SUM(OFFSET(C7,2,3,4,5))
In this example, we start from C7 and then go 2 rows down because it is positive. If it was negative, we would have gone up. Then we go 3 columns to the right because it is positive. If it was negative, we would have gone left. Now from this cell, we select 4 rows and 5 columns and that becomes our result range. Then, we call SUM on this range.
match - searches for a value in an array and returns the relative position
syntax: MATCH(value to search, array to search in, match type)
example: =MATCH(B13,OFFSET(C12,1,1,4,1),0)
In this example, match will search the value in cell B13 in the array range provided by offset function. Match type 0 means it will return the relative position of the first value that is equal to the value in cell B13. Match type -1 would mean that it will find the smallest value that is greater than or equal to value in B13. Match type 1 would mean that it will find the largest value that is less than or equal to the value in B13. 1 is default.
index -
syntax: index(range, row, col)
example:=INDEX(D18:G20,2,3)
In this example, the index function will return the value in row 2 and col 3 in the range D18:G20 i.e value in F19 cell.
vlookup -
syntax: vlookup(value in first col, range, col to return value from, boolean match)
example: =VLOOKUP(3,D22:G27,4,FALSE)
In this example, vlookup will search the cell with value 3 in the first column of range D22:G27. Then for that row, it will go to the 4th column and return the value. boolean match is to control the case whether an exact match for 3 is found or not. True means that if 3 is not found in the first col then return the next largest value which is less than 3. False means find the exact match = 3 in the first column.
split -
syntax: split(cell range, separator)
example: =split(A1," ")
split will split the cell value into multiple columns based on the separator value.
split -
syntax: split(cell range, separator)
example: =split(A1," ")
split will split the cell value into multiple columns based on the separator value.
Helpful Tips
- To insert multiple rows in between, add first row by right clicking and insert and then use the key 'F4' to keep on inserting.