SEM - I MATERIALS

|| CLICK AND GET MATERIALS SUBJECT VICE ||

ALL EXCEL FORMULA FOR MID TERM EXAM

            :: TRIM ::

Purpose 

Remove extra spaces from text

Return value 

Text with extra spaces removed.

Syntax 

=TRIM (text)

Arguments 

• text - The text from which to remove extra space.

Usage notes 

TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at the start or end of the text.

      :: PROPER ::

Purpose 

Capitalize the first letter in each word

Return value 

Text in proper case.

Syntax 

=PROPER (text)

Arguments 

• text - The text that should be converted to proper case.

Usage notes 

Use PROPER to capitalize each word in a given string.

All letters in text will be converted to lower case before the first letter in each word is capitalized.

Numbers and punctuation characters are not affected.

 :: SUM ::

Purpose 

Add numbers together

Return value 

The sum of values supplied.

Syntax 

=SUM (number1, [number2], [number3], ...)

Arguments 

• number1 - The first item to sum.
• number2 - [optional] The second item to sum.
• number3 - [optional] The third item to sum.

Usage notes 

The SUM function sums values supplied as arguments (up to 255 arguments).

Arguments can be supplied as numbers, cell references, ranges, arrays, constants, and the results of other formulas or functions.

For example, SUM(C1:C3) adds all the numbers that are contained in cells C1 through C3, which is equivalent to SUM (C1, C2, C3)

 :: SUMIF ::

Purpose 

Sum numbers in a range that meet supplied criteria

Return value 

The sum of values supplied.

Syntax 

=SUMIF (range, criteria, [sum_range])

Arguments 

• range - The range of cells that you want to apply the criteria against.
• criteria - The criteria used to determine which cells to add.
• sum_range - [optional] The cells to add together. If sum_range is omitted, the cells in range are added together instead.

Usage notes 

When sum_range is omitted, the cells in range will be summed.

Text criteria, or criteria that includes math symbols, must be enclosed in double quotation marks (").

Numeric criteria can be supplied without quotation marks.

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.

To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

:: SUMIFS ::

Purpose 

Sum cells that match multiple criteria

Return value 

The sum of the cells that meet all criteria

Syntax 

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

Arguments 

• sum_range - The range to be summed.
• range1 - The first range to evaulate.
• criteria1 - The criteria to use on range1.
• range2 - [optional] The second range to evaluate.
• criteria2 - [optional] The criteria to use on range2.

Usage notes 

SUMIFS sums cells in a range that match supplied criteria. Unlike the SUMIF function, SUMIFS can apply more than one set of criteria, with more than one range. The first range is the range to be summed. The criteria is supplied in pairs (range/criteria) and only the first pair is required. For each additional criteria, supply an additional range/criteria pair. Up to 127 range/criteria pairs are allowed.

::  COUNT ::

===== NO PICS TO TAKE ==== SORRY ITS EASY 

Purpose 

Count numbers

Return value 

A number representing a count of numbers.

Syntax 

=COUNT (value1, [value2], ...)

Arguments 

• value1 - An item, cell reference, or range.
• value2 - [optional] An item, cell reference, or range.

Usage notes 

The COUNT function counts the number of numbers in supplied values. Values can be items, cell references, or ranges. For example, =COUNT(1, 2, "apple") returns 2. And COUNT(A1:A10) will count the number of numeric values in the range A1:A10.

Count can handle up to 255 additional values.

Use COUNTA to include text and logical values. Use COUNTIF to count based on criteria.

Notes

• Error values or text values that cannot be coerced into numbers are not counted
• The logical values TRUE and FALSE are not counted.
• Empty cells and text are not counted.

:: COUNTIF ::

Purpose 

Count cells that match criteria

Return value 

A number representing cells counted.

Syntax 

=COUNTIF (range, criteria)

Arguments 

• range - The range of cells to count.
• criteria - The criteria that controls which cells should be counted.

Usage notes 

The COUNTIF function in Excel counts the number of cells in a range that match the supplied criteria

Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not:

=COUNTIF(A1:A10,100) // count cells equal to 100
=COUNTIF(A1:A10,">32") // count cells greater than 32
=COUNTIF(A1:A10,"jim") // count cells equal to "jim"
=COUNTIF(A1:A10,"<"&B1) // count cells less than value in B1

:: COUNTIFS ::

Purpose 


Count cells that match multiple criteria




Return value 


The number of times criteria are met




Syntax 



=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)





Arguments 







range1 - The first range to evaulate.
criteria1 - The criteria to use on range1.
range2 - [optional] The second range to evaluate.
criteria2 - [optional] The criteria to use on range2.










Usage notes 



COUNTIFS counts the number of cells in a range that match supplied criteria. Unlike the COUNTIF function, COUNTIFS can apply more than one set of criteria, with more than one range. Ranges and criteria are applied in pairs, and only the first pair is required. For each additional criteria, you must supply another range/criteria pairs. Up to 127 range/criteria pairs are allowed.



:: COUNTA ::

Purpose 


Count the number of non-blank cells




Return value 


A number representing non-blank cells.




Syntax 



=COUNTA (value1, [value2], ...)





Arguments 







value1 - An item, cell reference, or range.
value2 - [optional] An item, cell reference, or range.










Usage notes 



Use the COUNTA function to count cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

COUNTA will also count items. For example, =COUNTA("a",1,2,3,"") returns 5.

Use COUNT to count numeric values only.

Count can handle up to 255 values.

:: COUNTBLANK ::


Purpose 


Count cells that are blank




Return value 


A number representing blank cells




Syntax 



=COUNTBLANK (range)





Arguments 







range - The range in which to count blank cells.










Usage notes 



Use the COUNTBLANK function to count blank cells in a range. For example, COUNTBLANK(A1:A10) will count the number of blank cells in the range A1:A10.

COUNTBLANK counts the number of cells in the range that don't contain any value and returns this number as the result. Cells that contain, text, numbers, errors, etc. are not counted.

Cells that contain formulas that return empty text ("") are considered blank and will be counted.

Cells that contain zero are considered not blank and will not be counted.

:: AVERAGE ::






Purpose 


Get the average of a group of numbers




Return value 


A number representing the average.




Syntax 



=AVERAGE (number1, [number2], ...)





Arguments 







number1 - A number or cell reference that refers to numeric values.
number2 - [optional] A number or cell reference that refers to numeric values.










Usage notes 



The AVERAGE function returns the average (arithmetic mean) of a group of supplied numbers. To calculate the average, Excel adds the numbers together and divides by the total number of numbers. For example, AVERAGE (2,4,6) returns 4.

Numbers can be supplied as numbers, ranges, named ranges, or cell references that contain numeric values. Up to 255 numbers can be supplied.

Note: the AVERAGE function will automatically ignore empty cells.

:: AVERAGEIF ::







Purpose 


Get the average of numbers that meet criteria




Return value 


A number representing the average.




Syntax 



=AVERAGEIF (range, criteria, [average_range])





Arguments 







range - One or more cells, including numbers or names, arrays, or references.
criteria - A number, expression, cell reference, or text.
average_range - [optional] The cells to average. When omitted, range is used.










Usage notes 



AVERAGEIF computes the average of the numbers in a range that meet the supplied criteria. Ifaverage_range is not supplied, the cells in range are averaged. If average_range is supplied, cells cells in average_range that correspond to cells in range are averaged.

To determine which cells are averaged, criteria is applied to range. Criteria can be supplied as numbers, strings, or references. For example, valid criteria could be 10, "10" ">10", or A1

:: AVERAGEIFS ::




Syntax

AVERAGEIFS(average_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)

The AVERAGEIFS function syntax has the following arguments:

Average_range    Required. 



One or more
cells to average, including numbers or names, arrays, or references that
contain numbers.



Criteria_range1, criteria_range2, …    Criteria_range1
is required, subsequent criteria_ranges are optional. 1 to 127 ranges in which
to evaluate the associated criteria.

Criteria1, criteria2, ...    



Criteria1
is required, subsequent criteria are optional. 1 to 127 criteria in the form of
a number, expression, cell reference, or text that define which cells will be
averaged. For example, criteria can be expressed as 32, "32",
">32", "apples", or B4.



:: SUBTOTAL ::





Purpose 


Get a subtotal in a list or database




Return value 


A number representing a specific kind of subtotal




Syntax 



=SUBTOTAL (function_num, ref1, [ref2], ...)





Arguments 







function_num - A number that specifies which function to use in calculating subtotals within a list. See table below for full list.
ref1 - A named range or reference to subtotal.
ref2 - [optional] A named range or reference to subtotal.










Usage notes 



Use the SUBTOTAL function to get a subtotal in a list or database. SUBTOTAL has the ability to use a variety of functions when subtotaling, including AVERAGE, COUNT, MAX, and others (see table below for a complete list). In addition, the SUBTOTAL function can either include or exclude values in rows that are not visible.

Notice that the SUBTOTAL function has "paired" settings (i.e. 1/101, 3/103, 9/109, etc.) to change behavior for hidden cells. When function_num is between 1-11, SUBTOTAL includes cells that are hidden. When function_num is between 101-111, SUBTOTAL excludes values that are hidden.

SUBTOTAL always ignores all cells that are hidden by filtering with Autofilter, so all cells that are "filtered out" will not be included in calculations, regardless of the function_num that is used.

To create a list with subtotals, you might want to use the Subtotal command in the Outline group on the Data tab in the ribbon. You can then modify the SUBTOTAL function if needed.

Available values for function_num:

Function
Include hidden
Ignore hidden
AVERAGE
1
101
COUNT
2
102
COUNTA
3
103
MAX
4
104
MIN
5
105
PRODUCT
6
106
STDEV
7
107
STDEVP
8
108
SUM
9
109
VAR
10
110
VARP
11
111


Notes:

When function_num is between 1-11, SUBTOTAL includes values that are hidden
When function_num is between 101-111, SUBTOTAL excludes values that are hidden
In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless offunction_num.
SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
SUBTOTAL is designed to work with vertical data values arranged vertically. In Horizontal ranges, values in hidden columns are always included.


::  CONCATENATE ::














Purpose 


Join text together




Return value 


Text joined together.




Syntax 



=CONCATENATE (text1, text2, [text3], ...)





Arguments 







text1 - The first text value to join together.
text2 - The seond text value to join together.
text3 - [optional] The third text value to join together.










Usage notes 



CONCATENATE can join up to 30 text items together.

Text items can be text strings, numbers, or cell references that refer to one cell.

Numbers are converted to text when joined. If you need to specify a number format for a number being joined, see the TEXT function.

The ampersand character (&) is an alternative to CONCATENATE. The result is the same, but the ampersand is more flexible, and creates formulas that are shorter and (arguably) easier to read.

:: LEFT ::







Purpose 


Extract text from the left of a string




Return value 


One or more characters.




Syntax 



=LEFT (text, [num_chars])





Arguments 







text - The text from which to extract characters.
num_chars - [optional] The number of characters to extract, starting on the left side of text. Default = 1.










Usage notes 



Use the LEFT function when you want to extract characters starting at the left side of text.

num_chars is optional and defaults to 1.

LEFT will extract digits from numbers as well. Keep in mind that number formatting (i.e. the currency symbol $) is not part of a number so is not counted or extracted.

:: RIGHT ::






Purpose 


Extract text from the right of a string




Return value 


One or more characters.




Syntax 



=RIGHT (text, [num_chars])





Arguments 







text - The text from which to extract characters on the right.
num_chars - [optional] The number of characters to extract, starting on the right. Optional, default = 1.










Usage notes 




Use the RIGHT function when you want to extract characters starting at the right side of text.

num_chars is optional and defaults to 1.
RIGHT will extract digits from numbers as well. Keep in mind that number formatting is not part of a and will not be extracted or counted.





PREPARED BY :- 

ABDULKADIR CHAMAN

DIV :- "A" || 2016-18 ||

Contact :- +91-7405457791

Email :- Abdulkadirmemon786@gmail.com

skips id :- abdul16@skips.in

:::::::::::::  Note :::::::::::::

if you want a make passport & pancard please contact me 

abdul :- +91-7405457791

DISCOUNT FOR ONLY SKIPS STUDENTS 

PASSPORT :- 1800/-

PANCARD :- 150 /-

we provide to all types of services 

Pancard 

Passpord

Rail booking

Air Booking 

Web site Developing 

Mobile Application Developing 

All types Of Language training Like as a PHP,ASP.NET,ANDROID,JAVA