Home
Learn
Forum
Others Links
Learn
Microsoft Word
Microsoft Excel
Dictionary
ContactMicrosoft Excel
<<<Previous Page
creating a summary report using subtotal
removing a summary report
using consolidate
creating chart using chart wizard
creating result sheet
creating payroll sheet
some important formulas
Conclusion of MS Excel
Bookscreating a summary report using subtotal
removing a summary report
using consolidate
creating chart using chart wizard
creating result sheet
creating payroll sheet
some important formulas
Conclusion of MS Excel
Dictionary
Forum
Others Links
Time -
Clock
Forgot Password?
Chapter 67
some important formulas
Formulas are here :
| Name | Formulae |
|---|---|
| Cell Value | =A1 Returns cell value. |
| Summation | =SUM(A1:A15) Or, =SUM(A1:H10) Or, =SUM(A1,B2,H6) |
| Average | =AVERAGE(A1:A15) Or, =AVERAGE(A1:H10) Or, =AVERAGE(A1,B2,H6) |
| Minimum | =MIN(A1:A15) Or, =MIN(A1:H10) Or, =MIN(A1,B2,H6) |
| Maximum | =MAX(A1:A15) Or, =MAX(A1:H10) Or, =MAX(A1,B2,H6) |
| Product | =PRODUCT(A1:A15) Or, =PRODUCT(A1:H10) Or, =PRODUCT(A1,B2,H6) |
| Power | =POWER(A1,5) |
| Power Sum | =SUM(A1:A10^3) Returns the summations of the cubes of the values. |
| Or | =OR(A1<5, A2="Nasir", A3>12) Returns false if all conditions are false and true otherwise. |
| And | =AND(A1<5, A2="Nasir", A3>12) Returns true if all conditions are true and false otherwise. |
| Not Equal | =NOT(A1=45) Or, A1<>45 Returns true if condition is false and vise-versa. |
| Condition | =IF(A2>50, "Ns", IF(A3>50, "Nr", "Np")) Returns first value if true and second value if false. |
| Conditional Sum | =SUMIF(A1:A6,">20") Or, =SUMIF(A1:A6,">20",B1:B6) |
| Count | =COUNT(A1:A15) Counts the number of cells that have numerical value. |
| Nonempty Count | =COUNTA(A1:A15) Counts the number of cells that is not empty. |
| Large | =LARGE(A1:H15,5) Returns the 5th highest value. |
| Small | =SMALL(A1:H15,3) Returns the 2nd smallest value. |
| Concatenate | =CONCATENATE(45," ",Nasir") Adds the values. |
| Convert | =CONVERT(A1,"km","mi") Converts the value from kilometer to mile. More symbols are tbs=table spoonful, tsp=tea spoonful, yr=year, day=day, hr=hour, mn=minute, lbm=quid, C=degree celsius, K=degree kelvin, F=degree farenheight etc. |
| Year | =YEAR("8/22/2007") Returns year i.e. 2007. hour, minute second etc. are same. |
| Vlookup | =VLOOKUP(A1,H6:I10,2) Or, =VLOOKUP(A1,H6:I10,2,FALSE) Returns first match's value of the from the second column but match will be searched in first column. The first formulae finds closest match and the second exact match. |
| Left | =LEFT(A1,3) Returns the first 3 characters of the value. |
| Right | =RIGHT(A1,3) Returns the last 3 characters of the value. |
| Middle | =MID(A1,3,5) Returns the 5 characters of the value starting from 3rd character. |
| Now | =NOW() Returns the time of this moment. |
| Today | =TODAY() Returns the date of today. |
Note: There must not have any spaces in any formula though for good looking I have represent some spaces. To use Power Sum function you must have Analysis Toolpack add-ins activated. To use Vlookup function you must have the lookup range in ascending order.
Was this helpful for you?
