Omfgwtfbbq

[jnuts]

IS Functions
See Also

This section describes the nine worksheet functions used for testing the type of a value or reference.

Each of these functions, referred to collectively as the IS functions, checks the type of value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if value is a reference to an empty cell; otherwise it returns FALSE.

Syntax

ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)

Value is the value you want tested. Value can be a blank (empty cell), error, logical, text, number, or reference value, or a name referring to any of these, that you want to test.

Function Returns TRUE if
ISBLANK Value refers to an empty cell.
ISERR Value refers to any error value except #N/A.
ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL Value refers to a logical value.
ISNA Value refers to the #N/A (value not available) error value.
ISNONTEXT Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
ISNUMBER Value refers to a number.
ISREF Value refers to a reference.
ISTEXT Value refers to text.


Remarks

The value arguments to the IS functions are not converted. For example, in most other functions where a number is required, the text value "19" is converted to the number 19. However, in the formula ISNUMBER("19"), "19" is not converted from a text value, and the ISNUMBER function returns FALSE.


The IS functions are useful in formulas for testing the outcome of a calculation. When combined with the IF function, they provide a method for locating errors in formulas (see the following examples).

Examples

ISLOGICAL(TRUE) equals TRUE

ISLOGICAL("TRUE") equals FALSE

ISNUMBER(4) equals TRUE

Suppose C1:C5 on a worksheet of gold prices in different regions shows the following text values, number values, and error values: "Gold", "Region1", #REF!, $330.92, #N/A, respectively.

ISBLANK(C1) equals FALSE

ISERROR(C3) equals TRUE

ISNA(C3) equals FALSE

ISNA(C5) equals TRUE

ISERR(C5) equals FALSE

ISNUMBER(C4) equals TRUE (if the $330.92 was entered as a number and not as text)

ISREF(Region1) equals TRUE (if Region1 is defined as a range name)

ISTEXT(C2) equals TRUE (if Region1 is formatted as text)

On another worksheet, suppose you want to calculate the average of the range A1:A4, but you can't be sure that the cells contain numbers. The formula AVERAGE(A1:A4) returns the #DIV/0! error value if A1:A4 does not contain any numbers. To allow for this case, you can use the following formula to locate potential errors:

IF(ISERROR(AVERAGE(A1:A4)),"No Numbers",AVERAGE(A1:A4))

 

[OMGWTFBBQ]

Thanks - I deleted my thread because I found said info.

It would also be nice to know how to do a range for a variable sized column.

If I only wanted a set amount of information, it is easy to get. But I have a changing length of data that makes it harder.

If I want to sum a short list of shit, I can say SUM(A1:A5) and that will do it, no prob.

But what if I want a whole column of data - well, that is actually easy. I can just do SUM(A:A) and that will get it all, and that works.

But my problem (well, in Excel - I have lots of problems that outside of the scope of technical issues), is that I want to start at say A8 and then get everything after that.
That means that it might go until A10, or it might go until A15300

I have yet to figure out a way around that just programatically - I am currently getting around it with a hack by adding in an intermediary worksheet where calculations are staged.

 

[jnuts]

I have yet to figure out a way around that just programatically - I am currently getting around it with a hack by adding in an intermediary worksheet where calculations are staged.

Have you ever done any vbscript / vb for applications? That will hold your answer.

 

[OMGWTFBBQ]

I know VB very well - I used that to get the data into Excel in the first place off of the web.

If I wanted to do it all programatically that way - then I would just do it all in Perl (which I already have as a matter of fact - this is just an exercise really for me to learn more Excel).

But I want to do it all (or as much as possible) via Excel functions since they are already there (and fast).

The desired end result are the graphs and charts that I can grab from this.

It essentially pulls down data off of Yahoo! Finance, runs some calculations, charts the real data and then lists out its predictions of short term movement and also graphs that out N days ahead of today.

This is largely a statistical approach and it is easier to do the graphs quickly and easily in Excel than it is via Perl.

My Perl script does it for thousands of stocks and then outputs the N best, no charts.
This Excel spreadsheet will take in one stock and then output the charts and concepts for it.

It shows risk, error, and expected return over N days - potentially very useful and terribly fun for someone dorky like myself.

 

[OMGWTFBBQ]

Another issue that I am curious about in Excel is the following (I have asked the accountants at work - they use Excel all of the time and they didn't know how to do it):

Say I am in the first cell in a column. I type out a formula that I want that cell to contain - it works. I then want that formula to be in the next 5000 cells below this one.
I know that I can drag the formula down through those cells and it will populate it correctly, adjusting the references accordingly for each one as I go.

It is no big deal to do that for 10 rows, or even 100 rows - but it becomes more of a pain in the ass when you want to go several thousand rows.

Is there a quick way to apply that formula to the current cell and the N cells below it (where N gets large) and still have it adjust the references correctly?

 

[Lumberg]

Hey I haev an idea.

as far as I know the maximum number of rows possible in an Excel spreadsheet is somewhere near 65,000. You could do sum(a8:a65xxx) and you would have it.

find a file that is more than 66,000 lines long and try to import it into excel. you will get an error and then go to the end of the file and see how many rows you have. voila magic number.

Kloogy but it could just work!

Also I would like to nominate "SwollenColon" to your list of good user names (Teh real reason for this post)

Thank you

 

[jnuts]

But I want to do it all (or as much as possible) via Excel functions since they are already there (and fast).

Ok, I'm with ya now.

 

[OMGWTFBBQ]

Lumburg, you are correct - the maxrows for Excel is 65536 and the maxcols is 256.
(2^8 is 256 and 2^16 is 65636)

And you are right that if you just do a SUM with every row, assuming the rows are blank, then it will only give you the results of those that are populated and essentially ignore the rest.

I can do that at one point, but not at another point earlier... not sure I can put it into words.

But that essentially mildly helps me out of one issue (it is the way I was doing one thing), but it doesn't help with others.

 

[Lumberg]

Oooh I just read your thing about autofill.

I don't have Excel on this machine (I have oOo) but does this work:

Highlight first cell in range

hold down shift

highlight last cell in range (OK this part is only slightly easier to do if you still have to scroll down to the cell. However I think there is a function where you can just goto a specific cell).

use the autofill feature.

if autofill doesn't work like that just copy the contents of the cell you want coped nefore highlighting and then paste.

SwollenCOlon