Ms excel: istext function (WS)
Yüklə 192,66 Kb.
|
- Bu səhifədəki naviqasiya:
- Advanced Filter
- And Criteria
- Or Criteria
- Formula as Criteria
- MS EXCEL: ASC FUNCTION (VBA)
- Parameters or Arguments
- TYPE OF EXCEL FUNCTION
- MS EXCEL: ISBLANK FUNCTION (WS)
- APPLIES TO
- EXAMPLE (AS WORKSHEET FUNCTION)
- MS EXCEL: NETWORKDAYS FUNCTION (WS)
 MS EXCEL: ISTEXT FUNCTION (WS)
This Excel tutorial explains how to use the Excel ISTEXT function with syntax and examples. DESCRIPTION The Microsoft Excel ISTEXT function can be used to check for a text value. SYNTAX The syntax for the ISTEXT function in Microsoft Excel is: ISTEXT( value ) Parameters or Arguments value The value that you want to test. If value is a text value, this function will return TRUE. Otherwise, it will return FALSE. APPLIES TO The ISTEXT function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISTEXT function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel ISTEXT function examples and explore how to use the ISTEXT function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ISTEXT examples would return: =ISTEXT(A1)
=ISTEXT(A2) Result: TRUE =ISTEXT(A3) Result: FALSE =ISTEXT(A4) Result: TRUE =ISTEXT("techonthenet.com") Result: TRUE MS EXCEL: ISNUMBER FUNCTION (WS) This Excel tutorial explains how to use the Excel ISNUMBER function with syntax and examples. DESCRIPTION The Microsoft Excel ISNUMBER function can be used to check for a numeric value. SYNTAX The syntax for the ISNUMBER function in Microsoft Excel is: ISNUMBER( value ) Parameters or Arguments value The value that you want to test. If value is a numeric value , this function will return TRUE. Otherwise, it will return FALSE. APPLIES TO The ISNUMBER function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISNUMBER function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel ISNUMBER function examples and explore how to use the ISNUMBER function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ISNUMBER examples would return: =ISNUMBER(A1)
=ISNUMBER(A2) Result: TRUE =ISNUMBER(A3) Result: TRUE =ISNUMBER(A4) Result: FALSE =ISNUMBER(5) Result: TRUE =ISNUMBER("5") Result: FALSE MS EXCEL: ISERROR FUNCTION (WS, VBA) This Excel tutorial explains how to use the Excel ISERROR function with syntax and examples. DESCRIPTION The Microsoft Excel ISERROR function can be used to check for error values. SYNTAX The syntax for the ISERROR function in Microsoft Excel is: ISERROR( value ) Parameters or Arguments value The value that you want to test. If value is an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL), this function will return TRUE. Otherwise, it will return FALSE. APPLIES TO The ISERROR function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISERROR function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel ISERROR function examples and explore how to use the ISERROR function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ISERROR examples would return: =ISERROR(A1)
=ISERROR(A2) Result: TRUE =ISERROR(A3) Result: TRUE =ISERROR(A4) Result: FALSE =ISERROR("www.techonthenet.com") Result: FALSE =ISERROR(3/0) Result: TRUE MS EXCEL: ISDATE FUNCTION (VBA) This Excel tutorial explains how to use the Excel ISDATE function with syntax and examples. DESCRIPTION The Microsoft Excel ISDATE function returns TRUE if the expression is a valid date. Otherwise, it returns FALSE. SYNTAX The syntax for the ISDATE function in Microsoft Excel is: IsDate( expression ) Parameters or Arguments expression It is a variant that will be evaluated as a date. APPLIES TO The ISDATE function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISDATE function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS VBA FUNCTION) The ISDATE function can only be used in VBA code in Microsoft Excel. Let's look at some Excel ISDATE function examples and explore how to use the ISDATE function in Excel VBA code: IsDate("1/3/2004") Result: TRUE IsDate("Tech on the Net") Result: FALSE IsDate("January 3, 2004") Result: TRUE For example: Dim LValue As Boolean LValue = IsDate("Tech on the Net") MS EXCEL: ISNULL FUNCTION (VBA) This Excel tutorial explains how to use the Excel ISNULL function with syntax and examples. DESCRIPTION The Microsoft Excel ISNULL function returns TRUE if the expression is a null value. Otherwise, it returns FALSE. SYNTAX The syntax for the ISNULL function in Microsoft Excel is: IsNull( expression ) Parameters or Arguments expression A variant that contains a string or numeric value. APPLIES TO The ISNULL function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISNULL function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS VBA FUNCTION) The ISNULL function can only be used in VBA code in Microsoft Excel. Let's look at some Excel ISNULL function examples and explore how to use the ISNULL function in Excel VBA code: IsNull(null) Result: TRUE IsNull("Tech on the Net") Result: FALSE For example: Dim LValue As Boolean LValue = IsNull("Tech on the Net") MS EXCEL: ISNA FUNCTION (WS) This Excel tutorial explains how to use the Excel ISNA function with syntax and examples. DESCRIPTION The Microsoft Excel ISNA function can be used to check for a #N/A (value not available) error. SYNTAX The syntax for the ISNA function in Microsoft Excel is: ISNA( value ) Parameters or Arguments value The value that you want to test. If value is a #N/A error value , this function will return TRUE. Otherwise, it will return FALSE. APPLIES TO The ISNA function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISNA function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel ISNA function examples and explore how to use the ISNA function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ISNA examples would return: =ISNA(A1)
=ISNA(A2) Result: TRUE =ISNA(A3) Result: FALSE =ISNA(A4) Result: FALSE =ISNA("www.techonthenet.com") Result: FALSE =ISNA(3/0) Result: FALSE MS EXCEL: ISERR FUNCTION (WS) This Excel tutorial explains how to use the Excel ISERR function with syntax and examples. DESCRIPTION The Microsoft Excel ISERR function can be used to check for error values. SYNTAX The syntax for the ISERR function in Microsoft Excel is: ISERR( value ) Parameters or Arguments value The value that you want to test. If value is an error value (except #N/A), this function will return TRUE. Otherwise, it will return FALSE. APPLIES TO The ISERR function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ISERR function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel ISERR function examples and explore how to use the ISERR function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ISERR examples would return: =ISERR(A1)
=ISERR(A2) Result: FALSE =ISERR(A3) Result: TRUE =ISERR(A4) Result: FALSE =ISERR("www.techonthenet.com") Result: FALSE =ISERR(3/0) Result: TRUE MS EXCEL: MATCH FUNCTION (WS) This Excel tutorial explains how to use the Excel MATCH function with syntax and examples. DESCRIPTION The Microsoft Excel MATCH function searches for a value in an array and returns the relative position of that item. SYNTAX The syntax for the MATCH function in Microsoft Excel is: MATCH( value, array, [match_type] ) Parameters or Arguments value The value to search for in the array. array A range of cells that contains the value that you are searching for. match_type Optional. It the type of match that the function will perform. The possible values are:
Note:
APPLIES TO The MATCH function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The MATCH function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel MATCH function examples and explore how to use the MATCH function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following MATCH examples would return: =MATCH(10572,A2:A6,1)
=MATCH(10572,A2:A6) Result: 3 (it matches on 10571 since the match_type parameter has been omitted and will default to 1) =MATCH(10572,A2:A6,0) Result: #N/A (it doesn't find a match since the match_type parameter is set to 0) =MATCH(10573,A2:A6,1) Result: 4 =MATCH(10573,A2:A6,0) Result: 4 Let's look at how we can use wild cards in the MATCH function. 
Based on the Excel spreadsheet above, the following MATCH examples would return: =MATCH("A?ples", A2:A6, 0)
=MATCH("O*s", A2:A6, 0) Result: 2 =MATCH("O?s", A2:A6, 0) Result: #N/A MS EXCEL: LOOKUP FUNCTION (WS) This Excel tutorial explains how to use the Excel LOOKUP function with syntax and examples. DESCRIPTION The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the LOOKUP function: LOOKUP FUNCTION (SYNTAX #1) In Syntax #1, the LOOKUP function searches for value in the lookup_range and returns the value in the result_range that is in the same position. The syntax for the LOOKUP function in Microsoft Excel is: LOOKUP( value, lookup_range, [result_range] ) Parameters or Arguments
The value to search for in the lookup_range. lookup_range A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for value in this range. result_range Optional. It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data. Note:
APPLIES TO The LOOKUP function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The LOOKUP function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following LOOKUP examples would return: =LOOKUP(10251, A1:A6, B1:B6)
=LOOKUP(10251, A1:A6) Result: 10251 =LOOKUP(10246, A1:A6, B1:B6) Result: #N/A =LOOKUP(10248, A1:A6, B1:B6) Result: "Apples" LOOKUP FUNCTION (SYNTAX #2) In Syntax #2, the LOOKUP function searches for the value in the first row or column of the array and returns the corresponding value in the last row or column of the array. The syntax for the LOOKUP function in Microsoft Excel is: LOOKUP( value, array ) Parameters or Arguments
The value to search for in the array. The values must be in ascending order. array An array of values that contains both the values to search for and return. Note:
APPLIES TO The LOOKUP function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The LOOKUP function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel LOOKUP function examples and explore how to use the LOOKUP function as a worksheet function in Microsoft Excel: =LOOKUP("T", {"s","t","u","v";10,11,12,13})
=LOOKUP("Tech on the Net", {"s","t","u","v";10,11,12,13}) Result: 11 =LOOKUP("t", {"s","t","u","v";"a","b","c","d"}) Result: "b" =LOOKUP("r", {"s","t","u","v";"a","b","c","d"}) Result: #N/A =LOOKUP(2, {1,2,3,4;511,512,513,514}) Result: 512 FREQUENTLY ASKED QUESTIONS Question: In Microsoft Excel, I have a table of data in cells A2:D5. I've tried to create a simple LOOKUP to find CB2 in the data, but it always returns 0. What am I doing wrong? Answer: Using the LOOKUP function can sometimes be a bit tricky so let's look at an example. Below we have a spreadsheet with the data that you described. 
In cell F1, we've placed the following formula: =LOOKUP("CB2",A2:A5,D2:D5) And yes, even though CB2 exists in the data, the LOOKUP function returns 0. Now, let's explain what is happening. At first, it looks like the function isn't finding CB2 in the list, but in fact, it is finding something else. Let's fill in the empty cells in D3:D5 to explain better.
If we place the values TEST1, TEST2, TEST3 in cells D3, D4, D5, respectively, we can see that the LOOKUP function is in fact returning the value TEST2. So we ask ourselves, when we are looking up CB2 in the data and CB2 exists in the data, why is it returning the value for CB19? Good question. The LOOKUP function assumes that the data in column A is sorted in ascending order. If you look closer at column A, it is not in fact sorted in ascending order. If we quickly sorted column A, it would look like this:
Now the LOOKUP function correctly returns 3A when it is looking up CB2 in the data. To avoid these sorting problems with your data, we recommend using VLOOKUP function in this case. Let's show you how we would do this. If we changed our formula below (but left our data in column A in the original sort order):
The following VLOOKUP formula would return the correct value of 3A. =VLOOKUP("CB2",$A$2:$D$5,4,FALSE) The VLOOKUP function does not require us to have the data sorted in ascending order since we used FALSE as the last parameter - which means that it is looking for an exact match. Advanced Filter
And Criteria | Or Criteria | Formula as Criteria This example teaches you how to apply an advanced filter to only display records that meet complex criteria. When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create a Criteria range(blue border below for illustration only) above your data set. Use the same column headers. Be sure there's at least one blank row between your Criteria range and data set. And CriteriaTo display the sales in the USA and in Qtr 4, execute the following steps. 1. Enter the criteria shown below on the worksheet. 
2. Click any single cell inside the data set. 3. On the Data tab, in the Sort & Filter group, click Advanced.
4. Click in the Criteria range box and select the range A1:D2 (blue). 5. Click OK.
Notice the options to copy your filtered data set to another location and display unique records only (if your data set contains duplicates). Result.
No rocket science so far. We can achieve the same result with the normal filter. We need the Advanced Filter for Or criteria. Or CriteriaTo display the sales in the USA in Qtr 4 or in the UK in Qtr 1, execute the following steps. 6. Enter the criteria shown below on the worksheet. 7. On the Data tab, click Advanced, and adjust the Criteria range to range A1:D3 (blue). 8. Click OK. 
Result.
Formula as CriteriaTo display the sales in the USA in Qtr 4 greater than $10.000 or in the UK in Qtr 1, execute the following steps. 9. Enter the criteria (+formula) shown below on the worksheet. 10. On the Data tab, click Advanced, and adjust the Criteria range to range A1:E3 (blue). 11. Click OK. 
Result.
Supposing I have a table of four columns that indicate four types of foodstuff: fruit, food, meat and drink and below them are the specific food name. See the following screenshot: 
Now I need to create one drop down list that contains the foodstuff, such as fruit, food, meat and drink and the second dropdown would have the specific food name. If I select food, the second drop down will show rice, noodle, bread and cake. To do this, please apply the following steps:
(1.) Create a range name for the categories, the first row, select the A1:D1, and type the range name Foodstuff into the Name Box, then press Enter key. 
(2.) Then you need to name the range for each of the columns as above step as shown below: 
2. Now I can create the first drop down list, please select a blank cell or a column that you want to apply this drop down list, and then click Data > Data Validation > Data Validation, see screenshot: 
3. In the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =Foodstuff into the Source box. See screenshot: 
Note: You need type in the formula what you named your categories. 4. Click OK and my first drop down list have been created, then select the cell and drag the fill handle to the cell that you want to apply this option. 
5. Then I can create the second drop down list, select one blank cell, and click Data > Data Validation > Data Validation again, in the Data Validation dialog box, click Settings tab, choose List from the Allow drop down list, and input this formula =indirect(F1) into the Source box, see screenshot: 
Note: F1 indicates the cell location for the first drop down list I have created, you can change it as your need. 6. Then click OK, and drag the cell content downwards, and the dependent drop down list have been created successfully. See screenshot: 
And then if I choose one type of the foodstuff, the corresponding cell will only display its specific food name. Notes: 1. The drop-down arrow is visible only when the cell is active. 2. You can continue going deeper as you like, if you want to create the third drop down list, just use the second dropdown as the Source of the third dropdown.
The following tools will greatly save your time and effort, which one do you prefer? Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer. Kutools for Excel: 120 powerful new functions for Excel, Increase your productivity in 5 minutes. Save two hours every day! Classic Menu for Office: Bring back familiar menus to Office 2007, 2010, 2013 and 365, as if it were Office 2000 and 2003. Kutools for Excel  Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
More than 120 powerful advanced functions which designed for Excel:
MS EXCEL: ABS FUNCTION (WS, VBA) This Excel tutorial explains how to use the Excel ABS function with syntax and examples. DESCRIPTION The Microsoft Excel ABS function returns the absolute value of a number. SYNTAX The syntax for the ABS function in Microsoft Excel is: ABS( number ) Parameters or Arguments number A numeric value used to calculate the absolute value. APPLIES TO The ABS function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTION The ABS function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION) Let's look at some Excel ABS function examples and explore how to use the ABS function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ABS examples would return: =ABS(A1)
=ABS(A2)
Result: 2.9 =ABS(A3)
Result: 3 =ABS(-4.5) Result: 4.5 =ABS(-210.67) Result: 210.67 =ABS(5)
Result: 5 =ABS(2-10) Result: 8 EXAMPLE (AS VBA FUNCTION) Now, let's look at more Excel ABS examples and explore how to use the ABS function in Excel VBA code. For example: Dim LNumber As Double LNumber = Abs (-210.6) In this example, the variable called LNumber would now contain the value of 210.6.
DESCRIPTIONThe Microsoft Excel ASC function returns the ASCII value of a character or the first character in a string. SYNTAXThe syntax for the ASC function in Microsoft Excel is: Asc( string ) Parameters or Argumentsstring The specified character to retrieve the AscII value for. If there is more than one character, the function will return the AscII value for the first character and ignore all of the characters after the first. APPLIES TOThe ASC function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTIONThe ASC function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS VBA FUNCTION)The ASC function can only be used in VBA code in Microsoft Excel. Let's look at some Excel ASC function examples and explore how to use the ASC function in Excel VBA code: Asc ("W")
Asc ("Wednesday") Result: 87 Asc ("x") Result: 120 MS EXCEL: ISBLANK FUNCTION (WS)This Excel tutorial explains how to use the Excel ISBLANK function with syntax and examples. DESCRIPTIONThe Microsoft Excel ISBLANK function can be used to check for blank or null values. SYNTAXThe syntax for the ISBLANK function in Microsoft Excel is: ISBLANK( value ) Parameters or Argumentsvalue The value that you want to test. If value is blank, this function will return TRUE. If value is not blank, the function will return FALSE. Note: See also the ISEMPTY function (VBA function).
APPLIES TOThe ISBLANK function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTIONThe ISBLANK function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION)Let's look at some Excel ISBLANK function examples and explore how to use the ISBLANK function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following ISBLANK examples would return: =ISBLANK(A1) Result: FALSE =ISBLANK(A2)
=ISBLANK("Tech on the Net") Result: FALSE MS EXCEL: NETWORKDAYS FUNCTION (WS)This Excel tutorial explains how to use the Excel NETWORKDAYS function with syntax and examples. DESCRIPTIONThe Microsoft Excel NETWORKDAYS function returns the number of "work days" between 2 dates, excluding weekends and holidays. SYNTAXThe syntax for the NETWORKDAYS function in Microsoft Excel is: NETWORKDAYS( start_date, end_date, [holidays] ) Parameters or Argumentsstart_date The start date to use in the calculation. It must be entered using the DATE function. end_date The end date to use in the calculation. It must be entered using the DATE function. holidays Optional. It is the list of holidays to exclude from the "work days" calculation. It can be entered either as a range of cells that contain the holiday dates (ie: E2:E4) or as a list of serial numbers that represent the holiday dates. APPLIES TOThe NETWORKDAYS function can be used in the following versions of Microsoft Excel:
TYPE OF EXCEL FUNCTIONThe NETWORKDAYS function can be used in Microsoft Excel as the following type of function:
EXAMPLE (AS WORKSHEET FUNCTION)Let's look at some Excel NETWORKDAYS function examples and explore how to use the NETWORKDAYS function as a worksheet function in Microsoft Excel: 
Based on the Excel spreadsheet above, the following NETWORKDAYS examples would return: =NETWORKDAYS(A2,B2)
=NETWORKDAYS(A2,B2,E4) Result: 9 (because it would exclude Easter Monday which is stored in cell E4) =NETWORKDAYS(A2,B2,DATE(2013,4,1)) Result: 9 (because it would exclude Easter Monday which is equal to the DATE(2013,4,1) formula) =NETWORKDAYS(A2,B2,E2:E4) Result: 8 (because it would exclude Good Friday and Easter Monday) =NETWORKDAYS(A3,B3) Result: 5 =NETWORKDAYS(A3,B3,E2:E4) Result: 4 (because it would exclude Easter Monday) =NETWORKDAYS(DATE(2013,3,24),DATE(2013,4,6)) Result: 10 Yüklə 192,66 Kb. Dostları ilə paylaş:
|
