Results 1 to 10 of 10
Thread: isblank function (XP)

20020730, 14:37 #1
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20020730, 15:56 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,313
 Thanks
 3
 Thanked 211 Times in 194 Posts
Re: isblank function (XP)
Hi,
There may be simpler solutions but the formula in the attached (which is arrayentered) should do what you want.
Hope that helps.Regards,
Rory
Microsoft MVP  Excel

20020730, 16:01 #3
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20020730, 16:10 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,313
 Thanks
 3
 Thanked 211 Times in 194 Posts
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?Regards,
Rory
Microsoft MVP  Excel

20020730, 16:22 #5
 Join Date
 Jan 2001
 Location
 Fontana, California, USA
 Posts
 625
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: isblank function (XP)
It worked  I actually get it. Thanks again. How are things in Jolly Old?

20020730, 23:26 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: isblank function (XP)
I'd suggest using this ordinary formula:
=B3INDEX(C3:G3,MATCH(9.99999999999999E+307,C3:G3))
instead of an expensive arrayformula.
Regards.
AladinMicrosoft MVP  Excel

20020731, 09:28 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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

20020731, 10:12 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: isblank function (XP)
Steve,
You shouldn't worry at all. The MATCH bit in
=B3INDEX(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.
AladinMicrosoft MVP  Excel

20020731, 15:07 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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!

20020801, 08:31 #10
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
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 wellknown 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
AladinMicrosoft MVP  Excel