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
Acknowledge(0)
Amend(0)
67From India, Madras
Acknowledge(0)Amend(0)
The Excel VLOOKUP function is rather useful. If you look below, you can see it in action. With the file using Internet Explorer.
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 searches for it in the first column in cells F3:G14. It then returns the corresponding value in the second column. In our example, the second column represents the sales budget column. Adding "FALSE" just ensures that Excel looks for exact matches.
Regards,
JA
From India, Madras
Acknowledge(0)Amend(0)
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 searches for it in the first column in cells F3:G14. It then returns the corresponding value in the second column. In our example, the second column represents the sales budget column. Adding "FALSE" just ensures that Excel looks for exact matches.
Regards,
JA
From India, Madras
Acknowledge(0)Amend(0)
Dear Joshua,
I want to know about the password for each individual cell in an Excel sheet (not the whole document/sheet or a group of cells in a row/column for protection). That means in an Excel sheet, I want some cells to be locked and some cells to be open. Is this option available in Excel, and how can I achieve it? Also, could you please explain what macros are and how to use them?
From India, Hyderabad
Acknowledge(0)Amend(0)
I want to know about the password for each individual cell in an Excel sheet (not the whole document/sheet or a group of cells in a row/column for protection). That means in an Excel sheet, I want some cells to be locked and some cells to be open. Is this option available in Excel, and how can I achieve it? Also, could you please explain what macros are and how to use them?
From India, Hyderabad
Acknowledge(0)Amend(0)
Hi,
1. Select All or press Ctrl+A.
2. Press Ctrl+1 to go to Protection, remove the checkmark from Lock and Hidden.
3. Select the cell you want to protect.
4. Again, press Ctrl+1, go to Protection, then mark and check the boxes for Lock and Hidden.
5. Go to Tools, choose Protect Sheet, enter a password if desired, and click OK.
Now, you will see that the selected cells are protected.
Regards,
Satish G.
From India, Mumbai
Acknowledge(0)Amend(0)
1. Select All or press Ctrl+A.
2. Press Ctrl+1 to go to Protection, remove the checkmark from Lock and Hidden.
3. Select the cell you want to protect.
4. Again, press Ctrl+1, go to Protection, then mark and check the boxes for Lock and Hidden.
5. Go to Tools, choose Protect Sheet, enter a password if desired, and click OK.
Now, you will see that the selected cells are protected.
Regards,
Satish G.
From India, Mumbai
Acknowledge(0)Amend(0)
In Excel, the DCount function returns the number of cells in a column or database that contain numbers and meet a given criteria. The syntax for the DCount function is: DCount( range, field, criteria )
From India, Madras
Acknowledge(0)Amend(0)
From India, Madras
Acknowledge(0)Amend(0)
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 that are all referring 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 references 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. For example, 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 meets 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
Acknowledge(0)Amend(0)
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 that are all referring 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 references 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. For example, 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 meets 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
Acknowledge(0)Amend(0)CiteHR is an AI-augmented HR knowledge and collaboration platform, enabling HR professionals to solve real-world challenges, validate decisions, and stay ahead through collective intelligence and machine-enhanced guidance. Join Our Platform.
Excel Functions Dictionary.xls
Excel tips.pps