Excel Top 10 formula For The Beginners

1
(No Transcript)
2
Statanalytica
3
Statanalytica
4
Statanalytica
5
1. Count, Sum, Average
These are some of the most useful commands in
excel. Everyone who sees a spreadsheet knows
while working this visual information person need
these command very often and these are some of
the basic mathematics formulas.These basic
formulas are so important that they made at the
top of this excel top 10 formula list.
COUNTING OF CELL(Count, Counta).
COUNT
This command is useful when the user wants to
count the number of cells that contain numeric
values.
6
1. Count, Sum, Average
COMMAND KEYWORD
Count(2,3)
This command will give the number of values in
the brackets. For example, in Count(2,3) there
are only two values. This command only works with
numbers. This command will only work for numeric
values.
Count(B1, B2, B3)
This command will give the number of the cells
having a numeric value from selected cells
specified in the bracket. It will not count the
empty cells. This command only works with numbers.
7
1. Count, Sum, Average
COMMAND KEYWORD
Count(B1 B8)
This command will provide the number of values in
the range of cells starting from B1, B2, B3till
B8. For example in Count(B1 B8) the computer
will count the number of cells that contain any
value it will not count empty cells.
COUNTA(B1 B10)
This command is useful when a user wants to count
the number of non-empty cells in a range. This
command works with numeric as well as
alphabetical or any character. Besides, this
command can also be used in these formats for
examples such as Counta(B1, B2,) or Counta(B1
B10) and Counta(2,4,6).
8
2. IF statement
This command is used for conditional output.
Using this user can take any output from the
computer according to the requirement.
Command keyword IF(logical, output if logic is
true, output if logic is false).
Lets suppose an example that a company took a
target to increase its turnover in a year then
the management can follow strict rules in order
to meet the target. They can monitor themselves
through an excel sheet using a command like
IF(turnovergt100cr. Turnover achieved, Turnover
not achieved).
9
3. SUMIF, COUNTIF, AVERAGEIF
These commands are useful for the user when you
want to put a condition on some of the basic
commands like adding the data if the condition is
fulfilled. Count the data if the condition gets
fulfilled, Take an average of the data if the
condition is true.
Command Keyword SUMIF(Range, Criteria,
Sum_range),
COUNTIF(Range, Criteria),  AVERAGEIF(Range,
criteria) Furthermore, these conditional
commands are very useful so 3rd Position in our
excel top 10 formulas.
10
4. VLOOKUP HLOOKUP
As the name of this command it allows users to
lookup for a value in an array. In VLOOKUP V
stands for vertical lookup. Similarly, in HLOOKUP
H stands for horizontal. Basically in these
commands we lookup for the values in an array.
Command Keyword- VLOOKUP(lookup value, table
array, column index number, range lookup).
And HLOOKUP((lookup value, table array, row index
number, range lookup ). So these are formulas
that will provide you flexibility in the excel
sheet. This command made it on the 4th position
on our list of excel top 10 formula.
11
5. Concatenate
This command is used to combine the characters of
two different cells. This is applicable when we
have to combine first name, last name and
combining addresses from a huge datasheet.
Command Keyword- VLOOKUP(lookup value, table
array, column index number, range lookup).
12
6. MAX and MIN
As the name suggests, the main function of both
the commands is to search for the maximum value
in a huge table, and the minimum value in a huge
table.
Command Keyword MAX(select range) and
MIN(select range).
13
7. AND
This is one very useful function. The user can
use this function to check if any condition is
true or false. There could more than one
condition.
Command Keyword AND(logic 1, logic 2,).
For example, AND(A1GOOD,C3gt5) output would be
TRUE if A1 is GOOD and the value of C3 is greater
than 5.
14
8. PROPER
This command is useful when you have huge data
which is oddly formatted. if a cell has tiMe iS
moNeY clearly the sentence needs formatting. so
the user can use the proper command, for example,
PROPER(cell_number) and it would output a
well-formatted sentence in the cell.
Command keyword PROPER(cell no1., cell no2), or
PROPER(cel no1 cell no10).
15
9.Conditional formatting
This command is useful if the user wants to
format things with the condition. for example-
lets suppose we have a table or huge data. And
in our example figure we students marks and we
want to see how many students got less than 50.
16
10. UPPER, LOWER, TRIM.
These commands are useful when the user has huge
data that is not well structured and
well-formatted. All of these commands define its
function. The lower feature has used to convert
all the characters in small letters within a
cell. The upper feature will convert all the
characters in Capital letters within a cell. Trim
will remove all the space within a cell.
Command keywords LOWER(cell_number) UPPER(cell
number) TRIM(cell number)
17
Conclusion
These are all the excel top 10 formula that
everyone should know in order to boost excel
skills. Each of these commands is very basic. If
you want to start improving your excel skills,
then you must concentrate on these basic excel
top 10 formula. Each of these commands gets
complex as you want to perform more complicated
tasks on the excel. The importance will be more
clear once you perform these formula by yourself
then you will understand why we added them in the
list for excel top 10 formula.
Statanalytica
18
Follow us on Social Media
19
We are Everywhere
GET IN TOUCH
WEBSITE
https//statanalytica.com
EMAIL ADDRESS
info_at_statanalytica.com