In this blog, We have discussed the different functions of Excel and many more...
Basic Excel skills include:
- Entering data.
- Formatting cells.
- Using basic formulas and functions.
- Creating simple charts.
- Sorting and filtering data.
- Understanding cell references.
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
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