In this blog, We have discussed the different functions of Excel and many more...
Basic Excel skills include:
Absolute cell referencing and Relative cell referencing
- Entering data.
- Formatting cells.
- Using basic formulas and functions.
- Creating simple charts.
- Sorting and filtering data.
- Understanding cell references.
--------------------------------------------------------------------------------------------------------------
Absolute cell referencing and Relative cell referencing
Absolute Cell Referencing:
• In absolute cell referencing, the cell reference remains constant or fixed when you copy or fill the formula to other cells.
• To create an absolute cell reference,
• you use a dollar sign ($) before both the column letter and the row number,
• like this: “$A$1”.
Relative Cell Referencing:
• The cell reference is adjusted relative to the position of the formula when you copy or fill it to other cells.
• By default, Excel uses relative referencing.
• To create a relative reference, no additional special symbols are required.
• When you copy a formula with relative references,
• Excel automatically changes the references based on the formula’s relative position.
• For example, if you copy a formula with “A1” from cell B1 to cell B2,
• it will become “A2.”
What is the difference between count, counta, and countblank?
1. COUNT
It counts the number of cells that contain numeric values only. Cells that have string values, special characters, and blank cells will not be counted.
for example: =count(A1:A10)
2. COUNTA
It counts the number of cells that contain any form of content. Cells that have string values, special characters, and numeric values will be counted. However, a blank cell will not be counted.
for example: =countA(A1:A10)
3. COUNTBLANK
As the name suggests, it counts the number of blank cells only. Cells that have content will not be taken into consideration.
for example: =countblank(A1:A10)
--------------------------------------------------------------------------------
QUARTILE
Quartiles are a type of quantiles which divide the number of data points into four parts, or quarters, of more-or-less equal size.
Data must be ordered from smallest to largest to compute the quartile.
=QUARTILE(Array, Quart)
0 Quart means Minimum Value
1 Quart means First Quartile
2 Quart means Median Quartile
3 Quart means Third Quartile
4 Quart means Maximum Value
QUOTIENT
A quotient is the result of division. The number above the line (the dividend) is divided by the number below the line (the divisor), and the answer is the quotient.
For example, in the problem 12 ÷ 3 = 4,
=QUOTIENT(12,3)
12 is the dividend, 3 is the divisor, and 4 is the quotient.
SUBTOTAL
A formula that calculates a range of values based on a specified operation.
It appears as =SUBTOTAL(function_num, ref1, [ref2],...), formula to perform.
where function_num refers to the operation you want the perform.
=SUBTOTAL(function name, Reference 1)
In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. As you can see, the function used is
=SUBTOTAL(1, A2: A4)
In the subtotal list “1” refers to average.
There are different fuctions available in SubTotal for example
1 Average
2 Count
3 CountA
4 Minimum
5 maximum
Total 20 different functions
SUM and SUMIF functions
• The SUM function is used to calculate the sum of all the values that are specified as a parameter to it.
• SUM(number1, number2, …)
• SUMIF: This function is used to calculate the sum of values that comply with a given condition.
• SUMIF(range, criteria, [sum_range])
• Where range specifies the range of cells to be evaluated criteria provide the condition to be met
• sum_range is optional and provides the actual cells to be summed up
RAND
By default, the RAND Excel function generates random numbers from 0 to 1.
However, we can generate numbers within any desired range, such as 0 to 100.
=RAND()
RANDBETWEEN
Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.
=RANDBETWEEN(Bottom value, Top value)
For example Randbetween(50, 100)
CORRELATION
Excel calculates the correlation coefficient. Correlation refers to the statistical relationship between the two entities.
The correlation coefficient is covariance divided by the product of the two variables' standard deviations. It measures the extent to which two variables are linearly related.
For example, the height and weight of a person are related, and taller people tend to be heavier than shorter people.
Correlations between variables can be positive, negative, or zero.
=CORREL(array1,array2)
MODULUS
The MOD() function works on returning the remainder when a particular number is divided by a divisor.
In the example, we have divided 10 by 3. The remainder is calculated using the function
=MODULUS =MOD(10,3)
LEFT, RIGHT, MID
The LEFT() function gives the number of characters from the start of a text string.
Meanwhile, the MID() function returns the characters from the middle of a text string, given a starting position and length.
Finally, the right() function returns the number of characters from the end of a text string.
=Left(Text, Number char)
=Mid(Text, start Number, Number char)
=Right(Text, Number char)
-----------------------------------------------------------------------------------Or follow my blog from the below link
0 Comments