These are some Excel functions commonly used:
* Today()
Just type "=today()" in a cell and it will display the current date.
* Hour(serial)
* IF
IF(logical_test, value_if_true, [value_if_false])
Example:
=IF(A2<75,"Failed","Passed")
*NOT
Syntax
NOT(logical)
Logical is a value or expression that can be evaluated to TRUE or FALSE.
Example:=
NOT(FALSE) Reverses FALSE (TRUE)
=NOT(3+1=4)
*=OR(logical1,logical2,...)
Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax
OR(logical1,logical2,...)Logical1,logical2,... are 1 to 255 conditions
Example:
OR(TRUE) One argument is TRUE (TRUE)
=OR(1+1=1,2+2=5) All arguments evaluate to FALSE (FALSE)
=OR(TRUE,FALSE,TRUE)
* T
Returns the text referred to by value.
Syntax
T(value)
Value is the value you want to test.
Remarks
If value is or refers to text, T returns value. If value does not refer to text, T returns "" (empty text).
Example:
=T(A2)
Whatever be the value of A2 it will be copied to where your active cell is positioned.
=TEXT(value, format text)
Description
The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. This function is useful in situations where you want to display numbers in a more readable format, or you want to combine numbers with text or symbols. For example, suppose cell A1 contains the number 41.5. To format the number as a dollar amount, you can use the following formula:
=TEXT(A1,"Php0.00")
In this example, Excel displays Php41.50.
* =VLOOKUP
The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:=VLOOKUP(1,A2:C10,3,TRUE)- Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.
PMT(rate,nper,pv,fv,type)
For a more complete description of the arguments in PMT, see the PV function.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
Type is the number 0 (zero) or 1 and indicates when payments are due.
Example:
A2=12%, B2=10, C2=10000
Then the Formula is =PMT(A2/12, B2, B3)
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax
RATE(nper,pmt,pv,fv,type,guess)
For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv is the present value— the total amount that a series of future payments is worth now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due.
This example is from the Excel Help:
A2=4: Years of the loan
A3=-200: Monthly payment
A4=8000: Amount of the loan
Formula Description
=RATE(A2*12, A3, A4) Monthly rate of the loan with the above terms (1%)
=RATE(A2*12, A3, A4)*12 Annual rate of the loan with the above terms (0.09241767 or 9.24%)