theBaronDe
Level 1 (8 points)
iWork

Q: Formula help: count the number of blanks in a column "until..."

namestringmetric
matthew5
mark1
markoptimusPrime
mark

 

luke2
matthewjamesLikesSteak
john3
lukelukingGood
matthew
luke
johnjohnnyPop
john

 

Hey guys--so I'm dealing with a bit of an issue in Numbers. I have a table in Numbers--the above table is a simplified representation of the relevant parts of the table. The left column "name" has a series of 12 repeated names and 0 missing values. The middle column "string" is a column that has lots of missing values per a formula based on another column that is not relevant to this problem. The ones that are not missing are all strings. The problem I'm having is with the right "metric" column:

 

I am trying to create a dynamic formula that will result in the numbers, and blanks, exactly as shown above. My goal is to create a formula that, for each row, counts the number of blanks in the "string" column until reaching a blank "string" that occurs next to the same "name" as in the row housing the formula--and if there is no such an occurrence, for it to be blank. So for instance, the second row (including the header row) has the name "matthew". The next time a blank "string" occurs next to the name "matthew" is in the tenth row. The number of blanks that occur between the second row and the ninth row is 5 (rows 2, 3, 5, 6, and 8). I have manually coded a small part of the table to use something like this (which would be housed in cell C2 in this example):

 

=IF(B2= "", COUNTBLANK(B2:B9), "")

 

This formula produces the correct number for the first data row (5), but it does not dynamically accomplish what I am trying to do since I'm trying to have the formula find the next blank "string" occurring next to the same "name" in the formula row.

 

I'm hoping someone has come across something like this before. I tried to go through all of the functions and formulas native to Numbers but so far I haven't found much success.

MacBook Pro (Retina, 15-inch, Mid 2015), OS X El Capitan (10.11.3)

Posted on Sep 7, 2016 2:48 PM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (5,012 points)
Mac OS X
A:

Hi BaronDe,

 

This is what I have come up with and as promised it is a little cumbersome.

I added a column (that can be hidden). This flags the name columns that have a blank string.

Screen Shot 2016-09-07 at 10.08.57 PM.png

The real work happens here.

Screen Shot 2016-09-07 at 10.20.12 PM.png

D4= COUNTBLANK(OFFSET($A$1,ROW(cell)−1,1,MATCH($A2,OFFSET($A$1,ROW(cell),2,ROWS($A, headers)−ROW(cell),columns),0),columns))

This relies on Columns A,B and C being as I have them.

The last OFFSET() creates a single column array in column C starting with the next row below and running to the end of the table.

MATCH() searches that array for the next case of the name in that row.

The first OFFSET() creates a single column array in column B that ranges from the current instance of the name to the next one that is next to a blank string.

COUNTBLANK() does its thing.

 

Your original metric column is on the right. I added data so it is no longer correct.

This formula fails when applied in a row with a string.

It throws an error when there are no other matching names.

I don't think these are fatal flaws.

 

Let me know if you have questions.

 

quinn

Posted on Sep 8, 2016 9:46 AM

Close
theBaronDe

Q: Formula help: count the number of blanks in a column "until..."

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Sep 7, 2016 4:00 PM in response to theBaronDe
    Level 5 (5,012 points)
    Mac OS X
    Sep 7, 2016 4:00 PM in response to theBaronDe

    Hi BaronDe,

     

    I need to ask, "Why are you trying to do this?" While doing what you ask is possible it is going to be a complex solution. It may be much easier to get your info in another way. What do these blanks indicate?

     

    You need to know that COUNTBLANK() will not count a cell that has a formula in it even if that formula resolves to "".

     

    quinn

  • by t quinn,Helpful

    t quinn t quinn Sep 7, 2016 4:30 PM in response to t quinn
    Level 5 (5,012 points)
    Mac OS X
    Sep 7, 2016 4:30 PM in response to t quinn

    You need to know that COUNTBLANK() will not count a cell that has a formula in it even if that formula resolves to "".

     

    It seems to be working for me.

     

    q

  • by theBaronDe,

    theBaronDe theBaronDe Sep 7, 2016 4:30 PM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 7, 2016 4:30 PM in response to t quinn

    A blank indicates an available, sequential selection--sequential in the sense that they all occur in row-order.

    Therefore, in row 2, when Matthew is the selector, the useful piece of information I'm trying to create is the number of selections that will occur until Matthew is the selector again. If Matthew is the selector again but the "string" column is not empty, then it means that that selection has been pre-determined to occur.

    So my first approach was to "count the blanks."

     

    I'm aware that the formula could be quite intense, but it would be helpful if the formula was completely generalizable.

  • by t quinn,Solvedanswer

    t quinn t quinn Sep 8, 2016 9:46 AM in response to theBaronDe
    Level 5 (5,012 points)
    Mac OS X
    Sep 8, 2016 9:46 AM in response to theBaronDe

    Hi BaronDe,

     

    This is what I have come up with and as promised it is a little cumbersome.

    I added a column (that can be hidden). This flags the name columns that have a blank string.

    Screen Shot 2016-09-07 at 10.08.57 PM.png

    The real work happens here.

    Screen Shot 2016-09-07 at 10.20.12 PM.png

    D4= COUNTBLANK(OFFSET($A$1,ROW(cell)−1,1,MATCH($A2,OFFSET($A$1,ROW(cell),2,ROWS($A, headers)−ROW(cell),columns),0),columns))

    This relies on Columns A,B and C being as I have them.

    The last OFFSET() creates a single column array in column C starting with the next row below and running to the end of the table.

    MATCH() searches that array for the next case of the name in that row.

    The first OFFSET() creates a single column array in column B that ranges from the current instance of the name to the next one that is next to a blank string.

    COUNTBLANK() does its thing.

     

    Your original metric column is on the right. I added data so it is no longer correct.

    This formula fails when applied in a row with a string.

    It throws an error when there are no other matching names.

    I don't think these are fatal flaws.

     

    Let me know if you have questions.

     

    quinn

  • by theBaronDe,

    theBaronDe theBaronDe Sep 8, 2016 9:48 AM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 8, 2016 9:48 AM in response to t quinn

    Quinn, you are a god among men. Thank you for taking the time to help me with this. I added in an IF statement to make it blank if a "string" is present, and an IFERROR statement to blank the errors that are inevitable for the last blank occurrence for each "name."

     

    It's a beauty on the outside, and a beast on the inside.

  • by t quinn,

    t quinn t quinn Sep 8, 2016 9:02 PM in response to theBaronDe
    Level 5 (5,012 points)
    Mac OS X
    Sep 8, 2016 9:02 PM in response to theBaronDe

    Hi BaronDe,

     

    Thanks for the praise (and the Check!). I just recently thought to use OFFSET() in this way. It can be a little mindbending to set up but it really got me clear on how to use it.

     

    I figured IF() and IFERROR() were in the cards. I tend to leave IFERROR() to the last because it can make it difficult to debug a formula.

     

    Have fun with Numbers!

     

    quinn