Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    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.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    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.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  5. #5
    5 Star Lounger
    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?

  6. #6
    3 Star Lounger
    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:

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

    instead of an expensive array-formula.

    Regards.

    Aladin
    Microsoft MVP - Excel

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    3 Star Lounger
    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

    =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.

    Aladin
    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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!

  10. #10
    3 Star Lounger
    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 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

    Aladin
    Microsoft MVP - Excel

Posting Permissions

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