Scrigroup - Documente si articole

Username / Parola inexistente      

Home Documente Upload Resurse Alte limbi doc  


AccessAdobe photoshopAlgoritmiAutocadBaze de dateCC sharp
CalculatoareCorel drawDot netExcelFox proFrontpageHardware
HtmlInternetJavaLinuxMatlabMs dosPascal
PhpPower pointRetele calculatoareSqlTutorialsWebdesignWindows
WordXml

AspAutocadCDot netExcelFox proHtmlJava
LinuxMathcadPhotoshopPhpSqlVisual studioWindowsXml

The IS functions

excel

+ Font mai mare | - Font mai mic



DOCUMENTE SIMILARE

Trimite pe Messenger
The IS functions
Financial functions - PMT
The power of charts
Name and color-code sheets
Create a 3d range
Custom number formats
The SUM functions
Add and delete sheets
import data into an Excel worksheet
Advanced formatting features


the IS functions

IS functions are used to test the type of value or reference found in a target cell. Each of these functions will check the type of value in a cell and, depending on the outcome, return a value of true or false. For example, the ISBLANK function returns the logical value true if the target value is a reference to an empty cell. If this is not the case, the function returns a logical false.




The IS functions are show in Table 1-1.

Function

Returns TRUE if

ISBLANK(value)

Value refers to an empty cell.

ISERR(value)

Value refers to any error value other than #N/A.

ISERROR(value)

Value refers to any error value (#NA, #VALUE, #DIV/0, #REF!, #NUM!, #NULL!, or #NAME?).

ISLOGICAL(value)

Value refers to a logical value.

ISNA(value)

Value refers to the #N/A (value not available) error.

ISNONTEXT(value)

Value refers to any item that is not text. (Note this function returns TRUE if value refers to a blank cell.)

ISNUMBER(value)

Value refers to a number.

ISREF(value)

Value refers to a reference.



ISTEXT(value)

Value refers to text.

Suppose you want to use a worksheet to calculate the average of a range of cells, and you need to know if the range contains numbers before trying to calculate the average. If the AVERAGE function returns an error because it encountered blank cells, which IS function should we use to verify the existence of an error?

Since what we're really doing here is looking for an error, the ISERROR function is our best choice. Take a look at display Sheet 4 in your workbook and examine cell E4. Why does it display the words, 'Blank Range'? Why do the cells above give the correct answers for MAX and MIN scores?

The reason is that the formula in cell E4 references a range of values in column C, instead of column B, where the data actually resides. This produces an error in the AVERAGE formula, which is handled by the combined action of the ISERROR and IF functions. Correct the function so it gives the average of the contents in column B:

=IF(ISERROR(AVERAGE(B2:B12)),'Blank
Range',AVERAGE(B2:B12))

Next, add the word MEDIAN to your worksheet in cell E5. Do you remember the difference between an arithmetic mean (average) and a median? An average adds up all the numbers in a set and divides that sum by the number of members in the set. When the number of members in a set is even, the average returns a value with a decimal remainder, representing the halfway point in the data. When a set contains an odd number of members, the whole number value at the halfway point will be the median. Let's say you want to run a calculation on a set using the MEDIAN function, and the number of members in a set is odd. How can you do this using the IF and IS functions? Of course, you will also have to use the COUNT function to determine the number of members in the set.

To use ISODD and ISEVEN, you must activate the Analysis ToolPak. Select Tools > Add-Ins and check Analysis ToolPak. If ToolPak isn't listed as an option, run the Excel Setup program and install it.

Figure 1-6: The Add-Ins dialog box
Figure 1-6: The Add-Ins dialog box

The formula used to choose between MEDIAN and AVERAGE in cell E5 is shown below. Of course, this example is a bit contrived; you probably wouldn't ever use this kind of logic for MEDIAN and AVERAGE calculations. It is, however, a good demonstration of how you can add decision-making abilities to your Excel formulas. Soon, the formulas you have written will be deciding, all by themselves, which formula to run in a given circumstance.

=IF(ISODD(COUNT(B2:B12)), MEDIAN(B2:B12),
AVERAGE(B2:B12))

You can even make the label in cell D5 smart as follows:

=IF(ISODD(COUNT(B2:B12)),'Median','Average')

Build these formulas yourself on Sheet 4 in the workbook and see how they work. If you remove the last name and score on the list, how will the formulas react?

IS functions are best used in formulas for testing an outcome of a calculation. Frequently, they are combined with the IF function to provide a graceful method of error handling on the worksheet.

notes about IS functions

Value arguments are not converted. For example, in most other functions where a number is required, Excel functions will convert '16' to the value 16. However, in the formula ISNUMBER('16'), '16' is not converted from its text value, and ISNUMBER returns false.






Politica de confidentialitate



DISTRIBUIE DOCUMENTUL

Comentarii


Vizualizari: 512
Importanta: rank

Comenteaza documentul:

Te rugam sa te autentifici sau sa iti faci cont pentru a putea comenta

Creaza cont nou

Termeni si conditii de utilizare | Contact
© SCRIGROUP 2021 . All rights reserved

Distribuie URL

Adauga cod HTML in site