1. ## isblank function (XP)

No sure isblank is what I need - help please. See attached - when I enter an enrollment figure in the first date column for each site, I'd like the difference total to reflect each addition, in turn. For example, on the 22nd I enter an amount and the difference column reflects the difference. Then the next day I enter the new enrollment figures - I want the difference column to change to reflect THIS difference, and so on. Thanks.

2. ## Re: isblank function (XP)

Hi,
There may be simpler solutions but the formula in the attached (which is array-entered) should do what you want.
Hope that helps.

3. ## Re: isblank function (XP)

May be but this will work just dandy. Thank you so much. I don't suppose you could explain exactly what the formula does, could you, so I could explain it to the person for whom I posted this question? If not, that's cool - of so, thanks again.

4. ## Re: isblank function (XP)

I can try... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
The NOT(isblank(...)) returns an array of True or False values (evaluating to 1 or 0 respectively), according to whether each column is blank - True if it isn't, False otherwise. The COLUMN(...) returns a simple array of column numbers. These two arrays are then multiplied together giving an array with either the column number, if the column is not blank, or a zero, if it is blank. The max function then picks the largest of these - i.e. the number of the last column with a value in it. The offset function then selects a cell, starting from columnB, that is offset by zero rows down and however many columns across - you have to subtract 2 because the calculations start from column B, the second column.
I hope that makes some sense?

5. ## Re: isblank function (XP)

It worked - I actually get it. Thanks again. How are things in Jolly Old?

6. ## Re: isblank function (XP)

I'd suggest using this ordinary formula:

=B3-INDEX(C3:G3,MATCH(9.99999999999999E+307,C3:G3))

Regards.

7. ## Re: isblank function (XP)

Be careful with the MATCH function with unsorted data. MATCH can give screwy results when the data is NOT in ascending order and excel thinks it is (default). When it scans your list it does NOT check all the values. It might not have problems in this particular case due to the "largeness" of the value, but I feel it is a bad practice to get into.
Steve

8. ## Re: isblank function (XP)

Steve,

You shouldn't worry at all. The MATCH bit in

=B3-INDEX(C3:G3,MATCH(9.99999999999999E+307,C3:G3))

is safe to use, as long as 9.99999999999999E+307 does not occur in any cell before the last cell in use.
This number is the biggest positive number in Excel's repertory, a number which is in fact very unlikely to occur.

Sorting is not an issue, because MATCH scans all values in order to find an approximate match for that huge number.
Upon failing to find one, it defaults to the last numeric cell it scanned and returns that cell's row or column number.

To paraphrase you: "It will not have any problems when target values are numbers, therefore it is a sound practice to get into."

As it is clear from above, this method applies only to a range of numeric type.

Thus,

[1]

=MATCH(9.99999999999999E+307,A:A)

works when column A consists of ordinary numbers, true dates, or true times.

If the range of interest is of "text type" and we want to find the last cell in use in that range,
we need to use something different.

Either:

[1a]

=MATCH("*",A:A,-1) if "*" does not occur in column A. Otherwise,

or:

[1b]

=MATCH(REPT("z",255),A:A)

The latter will not return the row/column number of the last cell if it contains a formula generated "".

Regards.

9. ## Re: isblank function (XP)

I agree that you shouldn't have a problem with match since your number is so large.

I was speaking with the practice of Using match on unsorted lists. (this is not really an issue with your formula).

I disagree about MATCH looking at every entry: MATCH will ONLY look at every entry IF the optional "match_type" is set to "0".

If it is set to "1" or left blank (default is "1") the data MUST be ASCENDING order or the correct match might NOT be found. If it is set to "-1" the data MUST be descending. You can prove this to yourself by having a list of random values (or text) containing a match. It will NOT always find the true match in these lists.

If excel thinks the list is sorted (match_type = "1" or "-1" or omitted), excel uses an "indexing' technique to go thru the list quicker. At every "pass" it looks at the "middle value" and keeps cutting the list in half. You can find a hit in a list of 1,000 entries in only 7 passes: much quicker than examining all 1000 entries!

10. ## Re: isblank function (XP)

Geez. I find it incredible that you judged it necessary to quote the Help file and

concluded a claim with which you disagree:

"I disagree about MATCH looking at every entry..."

from a casual description of mine:

"Sorting is not an issue, because MATCH scans all values in order to find an approximate

match for that huge number."

Scanning doesn't mean necessarily "looking at every entry."

MATCH(9.99999999999999E+307,Reference)

boils down to: "Please MATCH, find out where 9.99999999999999E+307 is in Reference. By the

way Reference is sorted in ascending order," while whether Reference is in fact sorted or

not is quite irrelevant. The point is of course to coerce MATCH to behave as if Reference

is sorted.

And I wasn't describing/revealing the algorithm MATCH uses either. That it is faster with

sorted sets of values I know. Why should I use it if it wasn't? Its programmers might be

using the well-known algorithm of quick sort or one kindred is very likely. You could have

applied the algorithm to the case in question and discovered for yourself why the construct

works. "At every "pass" it looks at the "middle value" and keeps cutting the list in half."

The latest right half will have the value of the last cell in use. MATCH then returns the

row/column number of the last cell that is left whether its value equals to

9.99999999999999E+307 or not. I'm happy that it does so. That's why it can be exploited in

the manner I described.

I might add as well that MATCH ignores empty cells in Reference. For that reason, I use

this construct instead of usual COUNT or volatile COUNTA in dynamic formulas that define a name for a

range. For a recent example, see:

http://www.mrexcel.com/board/viewtopic.php...16207&forum=2&7

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•