Dear My Fans, Simplify your Excel 2007....................... What are all the doubts in excel please note here i will get you exact solution. Regards, JA
From India, Madras
67From India, Madras
The Excel vlookup function is rather useful. If you look Below you can see it in action. With the file with internet explore
In this example, we have a table of sales budget figures for each month (in green). By changing the month number in cell D2 (in red) it gives the corresponding sales figure for the month in cell D6 (in blue).
To do this all that is required is the vlookup formula as follows:
=VLOOKUP(D2,F3:G14,2,false)
So what does that actually do? The function looks up the value in D2 and looks for it in the first column in cells F3:G14. It then returns the corresponding value in the second column. Our example the second column is the sales budget column. Adding "false" just ensures that Excel looks for exact matches.
Regards,
JA
From India, Madras
In this example, we have a table of sales budget figures for each month (in green). By changing the month number in cell D2 (in red) it gives the corresponding sales figure for the month in cell D6 (in blue).
To do this all that is required is the vlookup formula as follows:
=VLOOKUP(D2,F3:G14,2,false)
So what does that actually do? The function looks up the value in D2 and looks for it in the first column in cells F3:G14. It then returns the corresponding value in the second column. Our example the second column is the sales budget column. Adding "false" just ensures that Excel looks for exact matches.
Regards,
JA
From India, Madras
Dear joshu,
I want to know about the password for each (individual) cell in excel sheet.(not whole document/sheet or group of cells in a row/column for protection.)That means in an excel sheet I want some cell are locked and some cells are opened.is the option for the excel sheet or not ? and what are the macros and how to use them ?
From India, Hyderabad
I want to know about the password for each (individual) cell in excel sheet.(not whole document/sheet or group of cells in a row/column for protection.)That means in an excel sheet I want some cell are locked and some cells are opened.is the option for the excel sheet or not ? and what are the macros and how to use them ?
From India, Hyderabad
Hi,
1. Select All or Ctrl+A.
2. Ctrl+1 go to Protection remove the tick sign from Lock and Hidden.
3. Select the cell which u want to protect
4. Again, Ctrl+1 go to Protection mark than tick the sign to Lock and Hidden
5. Go To Tools - Proctect Sheet - If you to give a password type password. and click Ok.
Now, u will see the selected cells are protected.
Regards,
Satish G.
From India, Mumbai
1. Select All or Ctrl+A.
2. Ctrl+1 go to Protection remove the tick sign from Lock and Hidden.
3. Select the cell which u want to protect
4. Again, Ctrl+1 go to Protection mark than tick the sign to Lock and Hidden
5. Go To Tools - Proctect Sheet - If you to give a password type password. and click Ok.
Now, u will see the selected cells are protected.
Regards,
Satish G.
From India, Mumbai
In Excel, the DCount function returns the number of cells in a column or database that contains numbers and meets a given criteria.
The syntax for the DCount function is:
DCount( range, field, criteria )
From India, Madras
The syntax for the DCount function is:
DCount( range, field, criteria )
From India, Madras
DAVERAGE(Database,field,criteria)
DCOUNT(Database,field,criteria)
DCOUNTA(Database,field,criteria)
DGET(Database,field,criteria)
DMAX(Database,field,criteria)
DMIN(Database,field,criteria)
DSUM(Database,field,criteria)
Example:
Note: - column G shows how the formula has been written in column F.
From the example above you can see the most common database worksheet functions which are all refering to the database above (rows 5 to 15) but mainly extracting information from the “Rounds” column. The following explains the syntax of the formula.
Database = all the cells ( the range ) that are used to make up the list of related data. In the example above each formula refernces the range B5 to K15 because that is where all the data is. The first row of the database, B5, is the label for each column and is important for the formula to work as you will see in the criteria explanation.
Field = the column that is used in the formula. As mentioned above each formula is using the “Rounds” field to provide information. This word is in F1, F5 and can be written in the formula within quotations. ie DMIN could have been written as ‘=DMIN(B5:K15,”Rounds”,B1:K2) instead of the reference to F1.
Criteria = the range of cells that contains the conditions specified. I left each cell blank but could have made a condition within the cells B1 to K2. You can use any range for the criteria, as long as it contains at least one column label (this example used “Rounds”) and at least one cell below the column label for specifying a condition for the column.
The following is a brief description of each of the Worksheet Functions.
DAVERAGE - finds the average of all numeric amounts in the selected field that meet the specified criteria.
DCOUNT - counts all numeric amounts in the selected field that meet the specified criteria.
DCOUNTA - counts all items in the selected field that meet the specified criteria and are not blank cells.
DGET - finds the item in the selected field that meet the specified criteria. If two items are in the field then the #NUM error will be the answer.
DMAX - finds the maximum of all numeric amounts in the selected field that meet the specified criteria.
DMIN - finds the minimum of all numeric amounts in the selected field that meet the specified criteria.
DSUM - finds the total of all numeric amounts in the selected field that meet the specified criteria.
Regards,
JA
From India, Madras
DCOUNT(Database,field,criteria)
DCOUNTA(Database,field,criteria)
DGET(Database,field,criteria)
DMAX(Database,field,criteria)
DMIN(Database,field,criteria)
DSUM(Database,field,criteria)
Example:
Note: - column G shows how the formula has been written in column F.
From the example above you can see the most common database worksheet functions which are all refering to the database above (rows 5 to 15) but mainly extracting information from the “Rounds” column. The following explains the syntax of the formula.
Database = all the cells ( the range ) that are used to make up the list of related data. In the example above each formula refernces the range B5 to K15 because that is where all the data is. The first row of the database, B5, is the label for each column and is important for the formula to work as you will see in the criteria explanation.
Field = the column that is used in the formula. As mentioned above each formula is using the “Rounds” field to provide information. This word is in F1, F5 and can be written in the formula within quotations. ie DMIN could have been written as ‘=DMIN(B5:K15,”Rounds”,B1:K2) instead of the reference to F1.
Criteria = the range of cells that contains the conditions specified. I left each cell blank but could have made a condition within the cells B1 to K2. You can use any range for the criteria, as long as it contains at least one column label (this example used “Rounds”) and at least one cell below the column label for specifying a condition for the column.
The following is a brief description of each of the Worksheet Functions.
DAVERAGE - finds the average of all numeric amounts in the selected field that meet the specified criteria.
DCOUNT - counts all numeric amounts in the selected field that meet the specified criteria.
DCOUNTA - counts all items in the selected field that meet the specified criteria and are not blank cells.
DGET - finds the item in the selected field that meet the specified criteria. If two items are in the field then the #NUM error will be the answer.
DMAX - finds the maximum of all numeric amounts in the selected field that meet the specified criteria.
DMIN - finds the minimum of all numeric amounts in the selected field that meet the specified criteria.
DSUM - finds the total of all numeric amounts in the selected field that meet the specified criteria.
Regards,
JA
From India, Madras
Community Support and Knowledge-base on business, career and organisational prospects and issues - Register and Log In to CiteHR and post your query, download formats and be part of a fostered community of professionals.
Advanced Excel 2003.pdf
152120-very-use ful-hr-manageme nt-software-bui lt-excel-psa-hr -manager-mainta in-payrrol-exce l-soft.zip
Competancy--exc el---mts___smts _289.xls