4 Replies Latest reply: Jun 8, 2012 6:46 PM by Barry
parexcel Level 1 Level 1 (0 points)

Numbers is horsey about not tolerating null values, and yet I can't seem to find a way for the result of a formula to be the blank cell null value.

 

I need a formula that will sparsely replicate the cells of another column depending on a condition, such as

 

=if(iserror(search("AAPL",a1)),<blank>,b1)

 

which might replicate those portions of my trading profits which resulted from securities with "AAPL" in their name to a column separate from the column with all trading profits.

 

If I wanted zeros, I could use Excel.  Zero is not the same as null.  If I wanted an opaque and geeky way of suppressing the display of zeros, I could also use Excel.

 

On the other hand, Apple developers could use Excel for a while to overcome some of their arrogance.  If they had to do real work with real volumes and weren't allowed to have a personal life until they got it done, they might suddenly compromise their purist principles.

  • Wayne Contello Level 6 Level 6 (15,195 points)

    =if(iserror(search("AAPL",a1)),"",b1)

     

     

    two double quotes denote the empty string

  • Barry Level 7 Level 7 (29,210 points)

    Here's another approach.

    Picture 14.png

    It avoids the error generated by SEARCH in the absence of the search string by using LEN and SUBSTITUTE.

     

    D2 (and filled down to D10):   =IF(LEN(A2)>LEN(SUBSTITUTE(A2,D$1,"")),B2,"")

     

    Like Wayne's version, this formula returns a null string if the search string (retrieved from D$1) is not present in the original string in column A. The null string is not a 'blank' cell, but is as close as one can get, using a formula in Numbers.

     

    Regards,

    Barry

  • parexcel Level 1 Level 1 (0 points)

    Thanks for the replies.  But the strict typing combined with lack of any way to denote empty or null values of a certain type makes it really, really unfriendly.

     

    I was trying to total a range, or replicate a cascading running total down a column in sheet of content which was nicely formatted with white space between groups of entries.  Therefore, if a formula derived from column data to the left is copied all the way down a column, there are going to end up being zero values.  Shouldn't matter, and is easy to suppress zero display in er, uh, that other package claiming by its name to be excellent, but ...

     

    So I'll omit the details of griping about number, time and date formatting, or the number of clicks and layers required, or the lack of tool bar buttons or a way to customize or add buttons and just say did they hire people from Microsoft to come over and do this product?

     

    But eventually I just threw in the towel and let my column have lots of extraneous zero values, and spent an hour setting up a custom format that would suppress zeros, use parenthesized display for negatives, and keep two fixed decimal places.  This is otherwise known as standard accounting format and has been around at least a couple centuries I know of, so I'm just a little bit surprised it's not a choice in Numbers.

     

    Before everyone starts replying with all kinds of hints, yes I know all these things are there.  But if you want them together you have to build your own, and it can take a long time so if your time is money (I'm a consultant who bills hourly and spends unbillable hours trading, so time is definitely money) then the cost of use is orders of magnitude more than any cost of software.

     

    Maybe when I get it all set up and customized it will feel more effective.  There's plenty to like and it's really been since Trapeze -- whoa, going on 15 years ago? -- since someone realized a spreadsheet is not equivalent to a page.  But it's still rough around the edges and needs some serious tuning based on metering user actions.  Also, clicks and display of resulting selections seem to deviate from Mac standards in frustrating ways.  They're trying to give clicks in the same region different meanings depending on other context.  I understand the desire, but it's a UI no-no.

     

    Thanks again.

  • Barry Level 7 Level 7 (29,210 points)

    parexcel writes:

    "er, uh, that other package claiming by its name to be excellent"

     

    Not sure why you are being so coy about naming MS Excel; it is part of your user name after all.

     

    No matter. The advice around here has generally been to use the tool that fits the job.

     

    Regards,

    Barry

     

    PS: If you want to see changes in the way Number works, those wants should be expressed to Apple, not to the participants in these user-to-user communities. Feature requests may be made through Provide Numbers Feedback, found in the Application menu ("Numbers" menu in Numbers). The link will also take you to the feedback page.

    B