What is spread sheet?
A spreadsheet is a grid of rows and columns also called as a
worksheet, earlier, spreadsheets were created manually with paper
and pencil. The workspace was a desk. People had to use either lined
paper, graph paper or regular paper with lines drawn on it manually to
organize the columns of numbers on the sheet and manipulate them.
Calculating, manipulating and analyzing data present in the
spreadsheet had to be done manually.
Spread sheet programs and applications
1- production planning
2- persona management
3- marketing
4- payroll
5- accounting
Excel features-
According to Microsoft, excel is the most comprehensive spreadsheet
application available in the market. It is not just a tool for calculationg,
manipulating and analyzing data, but also a versatile organizational
tool and an excellent one for presenting information.
1- worksheet and graphics
2- data lists and databases
3- workbook
4- standardized user interface
5- data excel other application
Formulae in excel
The user can create formulae to perform calculations as simple as
adding values in 2 sells or as complex as finding how much a particular
value deviated from other values in set.
1- mathematical
2- statistical
3- date and time
4- logical
5- text
Mathematical-
SUM
Adds all the numbers in a range of cells.
Syntax
SUM (number1, number2,)
ABS
Returns the absolute value of a number. The absolute value of a number is the number
without its sign.
Syntax
ABS (number)
INT
Rounds a number down to the nearest integer.
Syntax
INT (number)
LOG
Returns the logarithm of a number to the base you specify.
Syntax
LOG (number, base
MOD
Returns the remainder after number is divided by divisor. The result has the same sign as
divisor.
Syntax
MOD (number, divisor)
PRODUCT
Multiplies all the numbers given as arguments and returns the product.
Syntax
PRODUCT (number1, number2,...)
ROUND
Rounds a number to a specified number of digits.
Syntax
ROUND (number, num_ digits)
ROUNDDOWN
Rounds a number down, toward zero.
Syntax
ROUNDDOWN (number, num_ digits)
ROUNDUP
Rounds a number up, away from 0 (zero).
Syntax
ROUNDUP (number, num_ digits)
SQRT
Returns a positive square root.
Syntax
SQRT (number)
Statistical-
AVERAGE
Returns the average (arithmetic mean) of the arguments.
Syntax
AVERAGE (number1, number2 ...)
MAX
Returns the largest value in a set of values.
Syntax
MAX (number1, number2...)
MIN
Returns the smallest number in a set of values.
Syntax
MIN (number1, number2...)
COUNT
Counts the number of cells that contain numbers and also numbers within the list of
arguments. Use COUNT to get the number of entries in a number field that's in a range or
array of numbers.
Syntax
COUNT (value1, value2...)
date and time-
DATE
Returns the sequential serial number that represents a particular date. If the cell format
was General before the function was entered, the result is formatted as a date.
Syntax
DATE (year, month, day )
DAY
Returns the day of a date, represented by a serial number. The day is given as an integer
ranging from 1 to 31.
Syntax
DAY (serial_ number)
NOW
Returns the serial number of the current date and time. If the cell format was General
before the function was entered, the result is formatted as a date.
Syntax
NOW ( )
TODAY
Returns the serial number of the current date. The serial number is the date-time code
used by Microsoft Excel for date and time calculations. If the cell format was General
before the function was entered, the result is formatted as a date.
Syntax
TODAY ( )
Logical -
IF
Returns one value if a condition you specify evaluates to TRUE and another value if it
evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
Syntax
IF (logical_ test, value_ if_ true, value_ if_ false)
AND
Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is
FALSE.
Syntax
AND (logical1, logical2 ...)
OR
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax
OR (logical1, logical2...)
NOT
Reverses the value of its argument. Use NOT when you want to make sure a value is not
equal to one particular value.
Syntax
NOT (logical)
Text –
PROPER
Capitalizes the first letter in a text string and any other letters in text that follow any
character other than a letter. Converts all other letters to lowercase letters.
Syntax
PROPER (text)
UPPER
Converts text to uppercase.
Syntax
UPPER (text)
LOWER
Converts all uppercase letters in a text string to lowercase.
Syntax
LOWER (text)
LEFT
LEFT returns the first character or characters in a text string, based on the number of
characters you specify.
RIGHT
RIGHT returns the last character or characters in a text string, based on the number of
characters you specify.
MID
MID returns a specific number of characters from a text string, starting at the position
you specify, based on the number of characters you specify.
CODE
Returns a numeric code for the first character in a text string. The returned code
corresponds to the character set used by your computer.
CHAR
Returns the character specified by a number. Use CHAR to translate code page numbers
you might get from files on other types of computers into characters.
REPLACE
Also applies to:
REPLACEB
REPLACE replaces part of a text string, based on the number of characters you specify,
with a different text string.
TRIM
Removes all spaces from text except for single spaces between words. Use TRIM on text
that you have received from another application that may have irregular spacing.
Syntax
TRIM (text)
SUBSTITUTE
Substitute’s new_ text for old_ text in a text string. Use SUBSTITUTE when you want to
replace specific text in a text string; use REPLACE when you want to replace any text
that occurs in a specific location in a text string.
Syntax
SUBSTITUTE (text, old_ text, new_ text, instance_ num)
=SUBSTITUTE(A2, "Sales", "Cost") Substitutes Cost for Sales (Cost Data)