+ Reply to Thread
Results 1 to 5 of 5

A simple (I hope!) ARRAY question regarding blanks versus zeroes

Hybrid View

  1. #1
    Registered User
    Join Date
    04-15-2013
    Location
    US of A
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question A simple (I hope!) ARRAY question regarding blanks versus zeroes

    First off, so very happy to have found this Forum!! Being able to tap into so much expertise is invaluable to me!

    Second, I feel so incredibly stupid - I know what I'm asking is fairly straightforward but I just can't figure it out.

    I have a very simple spreadsheet I maintain which lists active employees of a certain type. Another department in my company would like to receive a copy each month so they have an updated list.

    My spreadsheet has several columns that the other department doesn't need. I created another sheet in the same workbook and used a simple array formula to display columns A-P of the source sheet. I adjusted the formatting so it essentially looks just like the other, but without the unnecessary columns. My hope is to just periodically save this other sheet to a new workbook and email it once per month to the other department.

    The issue I can't resolve is this: my array formula sheet returns zeroes for any blanks in the source sheet. Is there a way to have it return blanks instead?

    Thanks in advance for your help!!

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: A simple (I hope!) ARRAY question regarding blanks versus zeroes

    You can use IF....... eg.

    =IF(YourFormula="","",YourFormula)
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    04-15-2013
    Location
    US of A
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: A simple (I hope!) ARRAY question regarding blanks versus zeroes

    Thanks for the suggestion - I actually followed the other by "benishiryo" before seeing yours. The formatting option solved. Thanks!

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,156

    Re: A simple (I hope!) ARRAY question regarding blanks versus zeroes

    hi there, welcome to the forum. if you have many kinds of formats in different columns, then do them separately. otherwise, select all the cells & right-click -> Format Cells -> Custom:
    General;;

    just ensure you have 2 semi-colons in each type of formats. fyi, the 1st portion is how you want to present positive numbers, 2nd for negative, 3rd for 0s, 4th for text. they are all separated by semi-colons. so i chose nothing for zeroes. the format for comma-separated values & brackets for -ve numbers for eg looks like this:
    #,##0.00_);[Red](#,##0.00)
    so to add in blank for zeroes:
    #,##0.00_);[Red](#,##0.00);

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Registered User
    Join Date
    04-15-2013
    Location
    US of A
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: A simple (I hope!) ARRAY question regarding blanks versus zeroes

    Perfect - Thank you very much! Some of my columns were General, a few were Date - in both cases I used the ;; and it worked just as you said. I had no idea this could be done and assume it would have to be a formula, not a formatting, issue.

    Thanks!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1