1) MIN,MAX,PRODUCT,SUM,SUMIFS,DATA TYPES(DATE,NUMBER,PERCENTAGE ETC.)
========================================================
MIN :- The Excel MIN function returns the smallest value from a supplied set of numeric values.
The syntax of the function is:
MIN( number1, [number2], ... )
================================================================
MAX :- The Excel MIN function returns the LARGEST value from a supplied set of numeric values.
The syntax of the function is:
MAX( number1, [number2], ... )
=================================================================
SUM , SUMIF :- I POST IN MID TERM BLOG
DATA TYPES :-
AS A SIMPLE IS JUST RIGHT CLICK THE ANY VALUE AND SHOW THE FORMET CELL >
=================================================================
2)AVERAGE,AVERAGEIF,AVERAGEIFS,ABS,LINKING DATA TO DIFFERENT SHEET
SHOW MID BLOG
3)COUNT,COUNTA,COUNTIF,COUNTIFS,TRIM,PROPER
SHOW MID BLOG
4)SUBTOTAL,MATCH,INDEX,CONCATENATE,LEFT,RIGHT*******
SHOW MID BLOG
=================================================================
Excel uses two types of cell references to create formulas. Each has its own purpose. Read on to determine which type of cell reference to use for your formula.
Relative Cell References
This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill.
Example:
=SUM(B5:B8)
changes to =SUM(C5:C8) when copied across to the next cell...
Absolute Cell References
Situations arise in which the cell reference must remain the same when copied or when using AutoFill. Dollar signs are used to hold a column and/or row reference constant.
Example:
In the example below, when calculating commissions for sales staff, you would not want cell B10 to change when copying the formula down. You want both the column and the row to remain the same to refer to that exact cell.
By using $B$10 in the formula, neither changes when copied.
================================================
UPPER :- Convert text to upper case
LOWER :- Convert text to LOWER case
CEILING :- Round a number up to the nearest specified multiple
FLOOR :- Round a number down to the nearest specified multiple
VLOOKUP
HLOOKUP
LOOKUP ALL ARE PRACTICAL BASE
=====================================================
7) IF,AND,OR,NOT,DATA VALIDATION,CONVERT,EXACT,FACT,FIND
IF :-
Purpose
Test for a specific condition
Return value
The values you supply for TRUE or FALSE
Syntax
=IF (logical_test, [value_if_true], [value_if_false])
=IF(C6>=70, "Pass", "Fail")
MULTIPLE IF OR NESTED IF
Nested IF statements
You may here the term "Nested IF" or "Nested IF statement". This refers to using more than one IF function so that you can test for more conditions and return more possible results. Each IF statement needs to be carefully "nested" inside another so that the logic is correct.
For example, the following formula can be used to assign an grade rather than a pass / fail result:
AND
Purpose
Test multiple conditions with AND
Return value
TRUE if all arguments evaluate TRUE; FALSE if not
Syntax
=AND (logical1, [logical2], ...)
=AND(A1>0,A1<5)
DATA VALIDATION :- IS PRACTICAL BASED
COVERT :-
Purpose
Convert measurement units
Return value
A number in the new measurement system
Syntax
=CONVERT (number, from_unit, to_unit)
Arguments
Usage notes
The CONVERT function converts a number in one measurement system to another. For example, you can use CONVERT to convert feet into meters, pounds into kilograms, gallons into liters, and for many other unit conversions.
The tables below show the various units that are available to the CONVERT function. In all cases, Unit can be used for either from_unit or to_unit.
| Weight and mass | Unit |
| Gram | "g" |
| Slug | "sg" |
| Pound mass (avoirdupois) | "lbm" |
| U (atomic mass unit) | "u" |
| Ounce mass (avoirdupois) | "ozm" |
| Distance | Unit |
| Meter | "m" |
| Statute mile | "mi" |
| Nautical mile | "Nmi" |
| Inch | "in" |
| Foot | "ft" |
| Yard | "yd" |
| Angstrom | "ang" |
| Pica | "pica" |
| Time | Unit |
| Year | "yr" |
| Day | "day" |
| Hour | "hr" |
| Minute | "mn" |
| Second | "sec" |
| Pressure | Unit |
| Pascal | "Pa" (or "p") |
| Atmosphere | "atm" (or "at") |
| mm of Mercury | "mmHg" |
| Force | Unit |
| Newton | "N" |
| Dyne | "dyn" (or "dy") |
| Pound force | "lbf" |
| Energy | Unit |
| Joule | "J" |
| Erg | "e" |
| Thermodynamic calorie | "c" |
| IT calorie | "cal" |
| Electron volt | "eV" (or "ev") |
| Horsepower-hour | "HPh" (or "hh") |
| Watt-hour | "Wh" (or "wh") |
| Foot-pound | "flb" |
| BTU | "BTU" (or "btu") |
| Power | Unit |
| Horsepower | "HP" (or "h") |
| Watt | "W" (or "w") |
| Magnetism | Unit |
| Tesla | "T" |
| Gauss | "ga" |
| Temperature | Unit |
| Degree Celsius | "C" (or "cel") |
| Degree Fahrenheit | "F" (or "fah") |
| Kelvin | "K" (or "kel") |
| Liquid measure | Unit |
| Teaspoon | "tsp" |
| Tablespoon | "tbs" |
| Fluid ounce | "oz" |
| Cup | "cup" |
| U.S. pint | "pt" (or "us_pt") |
| U.K. pint | "uk_pt" |
| Quart | "qt" |
| Gallon | "gal" |
| Liter | "l" (or "lt") |
The following abbreviated unit prefixes can be used with any metric unit.
| Prefix | Multiplier | Abbreviation |
| exa | 1E+18 | "E" |
| peta | 1E+15 | "P" |
| tera | 1E+12 | "T" |
| giga | 1E+09 | "G" |
| mega | 1E+06 | "M" |
| kilo | 1E+03 | "k" |
| hecto | 1E+02 | "h" |
| dekao | 1E+01 | "e" |
| deci | 1E-01 | "d" |
| centi | 1E-02 | "c" |
| milli | 1E-03 | "m" |
| micro | 1E-06 | "u" |
| nano | 1E-09 | "n" |
| pico | 1E-12 | "p" |
| femto | 1E-15 | "f" |
| atto | 1E-18 | "a" |
EXACT
FACT : - FACTORIAL NO
EX :- =FACT(5) >> 5*4*3*2*1
= 120
FIND :-
Purpose
Get the location of text in a string
Return value
A number representing the location of find_text.
Syntax
=FIND (find_text, within_text, [start_num])
Arguments
Usage notes
Use the FIND function to get the location of one text string inside another.
================================
8) CONDITIONAL FORMATTING,SORTING,
9) ADVANCE FILTER,AUTO FILTER,
10) GOAL-SEEK,SCENARIOS,SOLVER
BASED ON PRACTICAL
==================================
LEN :- FIND THE LENGHT OF TEXT
POWER :- FIND THE POWER OF ANY NO...
SUMPRODUCT :-
Purpose
Multiply, then sum arrays
Return value
The result of multipled and summed arrays
Syntax
=SUMPRODUCT (array1, [array2], ...)
Arguments
Usage notes
The SUMPRODUCT function works with arrays, but it doesn't require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.
When you first encounter SUMPRODUCT, it may seem boring, complex, and even pointless. But SUMPRODUCT is an amazingly versatile function with many uses. Because it will handle arrays gracefully and without complaint, you can use it to process ranges of cells in clever, elegant ways (see links to formula examples on this page).
To illustrate how SUMPRODUCT works, here are a few common examples.
SUMPRODUCT for conditional sums and counts
Assume you have some order data in A2:B6, with State in column A, Sales in column B:
| A | B | |
| 1 | State | Sales |
| 2 | UT | 75 |
| 3 | CO | 100 |
| 4 | TX | 125 |
| 5 | CO | 125 |
| 6 | TX | 150 |
Using SUMPRODUCT, you can count total sales for Texas ("TX") with this formula:
=SUMPRODUCT(--(A2:A6="TX"))
And you can sum total sales to Texas ("TX") with the this formula:
=SUMPRODUCT(--(A2:A6="TX"),B2:B6)
MORE THAN 300+ FORMULA AND FUNCTION
JUST CLICK MY OFFICIAL SITE
TO LEARN ADVANCE EXCEL JUST CLICK MY WEBSITES
THANKS AND REGARDS
ABDULKADIR CHAMAN
DIV :- "A"
PGDM 2016-18
CONTACT :- +91-7405457791
EMAIL :- abdul16@skips.in
NOTE PLEASE
IF U WANT TO MAKE A PASSPORT AND PANCARD AND OTHER ALL TYPES OF ONLINE SERVICES CONTACT ME
ABDUL -- +91-7405457791
RATE FOR SKIPS FRIENDS ONLY
PASSPORT :- 2000/- 10% DISCOUNT 1800/-
PANCARD :- 200/- 10% DISCOUNT 180/-
===============================
No comments:
Post a Comment