Supressing Zeros from null lookups

I am filling a 'Top Ten' table with data from another spreadsheet, however 
when there is no contents in the linked table, my report shows 0. I would 
have expected the default behaviour to leave the cell blank.
Is there anything short of an if statement to fix this? A preference I am 
overlooking?
0
8/8/2008 7:21:01 AM
excel 39879 articles. 2 followers. Follow

6 Replies
419 Views

Similar Articles

[PageSpeed] 6

That's how Excel treats empty cells - you need to do something like:

=3DIF(your_formula=3D"","",your_formula)

Hope this helps.

Pete

On Aug 8, 8:21=A0am, Gaijintendo <Gaijinte...@discussions.microsoft.com>
wrote:
> I am filling a 'Top Ten' table with data from another spreadsheet, howeve=
r
> when there is no contents in the linked table, my report shows 0. I would
> have expected the default behaviour to leave the cell blank.
> Is there anything short of an if statement to fix this? A preference I am
> overlooking?

0
pashurst (2576)
8/8/2008 8:01:25 AM
I was really hoping there was another way... 
I have made a table of file and sheetnames to allow easy chop and changing 
of externally linked files...
so I end up with this behemouth:
=if(indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" & 
row(A11))=0,"",indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" & 
row(A11)))

:'(

"Pete_UK" wrote:

> That's how Excel treats empty cells - you need to do something like:
> 
> =IF(your_formula="","",your_formula)
> 
> Hope this helps.
> 
> Pete
> 
> On Aug 8, 8:21 am, Gaijintendo <Gaijinte...@discussions.microsoft.com>
> wrote:
> > I am filling a 'Top Ten' table with data from another spreadsheet, however
> > when there is no contents in the linked table, my report shows 0. I would
> > have expected the default behaviour to leave the cell blank.
> > Is there anything short of an if statement to fix this? A preference I am
> > overlooking?
> 
> 
0
8/8/2008 8:15:01 AM
Hi

You could just suppress zeros with Tool>options>view>de-select zero values

-- 
Regards
Roger Govier

"Gaijintendo" <Gaijintendo@discussions.microsoft.com> wrote in message 
news:0E6B665E-79B6-41E0-A430-2D472FB74972@microsoft.com...
> I was really hoping there was another way...
> I have made a table of file and sheetnames to allow easy chop and changing
> of externally linked files...
> so I end up with this behemouth:
> =if(indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
> row(A11))=0,"",indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
> row(A11)))
>
> :'(
>
> "Pete_UK" wrote:
>
>> That's how Excel treats empty cells - you need to do something like:
>>
>> =IF(your_formula="","",your_formula)
>>
>> Hope this helps.
>>
>> Pete
>>
>> On Aug 8, 8:21 am, Gaijintendo <Gaijinte...@discussions.microsoft.com>
>> wrote:
>> > I am filling a 'Top Ten' table with data from another spreadsheet, 
>> > however
>> > when there is no contents in the linked table, my report shows 0. I 
>> > would
>> > have expected the default behaviour to leave the cell blank.
>> > Is there anything short of an if statement to fix this? A preference I 
>> > am
>> > overlooking?
>>
>> 
0
Roger
8/8/2008 8:32:32 AM
That would then apply to all cells in that workbook.
If you want it to apply only to this cell, Format Custom as

;;

Yes, just two semicolons

-- 
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message news:ek0kwHT%23IHA.3344@TK2MSFTNGP04.phx.gbl...
| Hi
|
| You could just suppress zeros with Tool>options>view>de-select zero values
|
| -- 
| Regards
| Roger Govier
|
| "Gaijintendo" <Gaijintendo@discussions.microsoft.com> wrote in message
| news:0E6B665E-79B6-41E0-A430-2D472FB74972@microsoft.com...
| > I was really hoping there was another way...
| > I have made a table of file and sheetnames to allow easy chop and changing
| > of externally linked files...
| > so I end up with this behemouth:
| > =if(indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
| > row(A11))=0,"",indirect("[" & ASSETS!$A$2 & "]" & ASSETS!$A$2 & "!B" &
| > row(A11)))
| >
| > :'(
| >
| > "Pete_UK" wrote:
| >
| >> That's how Excel treats empty cells - you need to do something like:
| >>
| >> =IF(your_formula="","",your_formula)
| >>
| >> Hope this helps.
| >>
| >> Pete
| >>
| >> On Aug 8, 8:21 am, Gaijintendo <Gaijinte...@discussions.microsoft.com>
| >> wrote:
| >> > I am filling a 'Top Ten' table with data from another spreadsheet,
| >> > however
| >> > when there is no contents in the linked table, my report shows 0. I
| >> > would
| >> > have expected the default behaviour to leave the cell blank.
| >> > Is there anything short of an if statement to fix this? A preference I
| >> > am
| >> > overlooking?
| >>
| >> 


0
nicolaus (2022)
8/8/2008 9:01:33 AM
This is all really appreciated, however, I tried ;; and it seems to supress 
any number, not simply zero. But I never even thought of using formatting to 
solve this.

cheers!
0
8/8/2008 9:38:01 AM
used #;; as I wont have any negative values.

Thanks so much¬

"Gaijintendo" wrote:

> This is all really appreciated, however, I tried ;; and it seems to supress 
> any number, not simply zero. But I never even thought of using formatting to 
> solve this.
> 
> cheers!
0
8/8/2008 9:44:00 AM
Reply:

Similar Artilces:

Filtered Lookups
Hi, I wanted to filter values in one lookup (Lkup2 ) based on the selection made in another lookup (Lkup1) Is there a way I can achieve this without 1 - going for any paid products or 2 - modifying the CRMWeb\_controls\lookup\lookupsingle.aspx page Thank you very much Check this out, it has it http://www.microsoft.com/downloads/details.aspx?familyid=634508DC-1762-40D6-B745-B3BDE05D7012&displaylang=en Have a look here: http://www.eggheadcafe.com/software/aspnet/35418198/how-to-restrick-a-lookup.aspx this thread was about filtering the lookup of "primary contact" to contacts ...

Multivalue with Null value SSRS 2005
I have a query to populate a multivalue parameter: SELECT distinct cast(AGRPYear.value as varchar(4)) + AGRPMonth.value 'ReportDate' FROM TPROJECT AS TPROJECT One of the values that is returned from this query is NULL. However, when I run the report, the NULL value does not show in the dropdown. I've also tried adding "select NULL as 'ReportDate' union" to the above query and the null value still doesn't show. As a result some of the records in my database have a null value for this field, they will never show up on my report. Any id...

Assertion in CWinApp::CWinApp
I have created an MFC project that links MFC MFC as shared dll and uses /MD[d] . This MFC application loads another dll B via Loadlibrary that has /MD[d] too and also links MFC as shared dll. Dll B again loads dll C that also uses MFC as a shared DLL dll and also has /MD[d]. Also the MFC app statically links to a .lib that has MFC linked as shared dll and has /MD[d] Now for some odd reason when the constructor of my MFC app is called (CMyMFCApp:CMyMFCApp) I get an assertion in CWinApp::CWinApp in this line: ASSERT(AfxGetThread() == NULL); (appcore.cpp). Any Idea what I possibly might...

Lookup function help please
I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help Remove the quotations marks around the 1 in AU17's formula: =IF(VC1="U",1, IF(VC1="...

Formatting NULL values in Drop-Down control
I have a drop-down control that is being populated by an entire table (not a query). There are NULL values in the table which appear as blank spaces in the drop down. How can I format the drop down to put some static text in place of the empty space. (For example 'Available', or 'Not Defined'). In the combobox's Row Source, use a query to return the list and instead of the original field, use something like: Nz([OriginalField],"Available") jtertin wrote: >I have a drop-down control that is being populated by an entire table >(not a query). There are...

Not to inlcude zero in COUNT
Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne Try =SUMPRODUCT((B1:B100=criteria1)*(C1:C100=criteria2)* (D1:D100=criteria3)*(ISNUMBER(N1:N100))*(N1:N100<>0)) With criterias in J1, J2, J3 try the below which will count the number of values in N1:N100 except zeroes and blanks =SUMPRODUCT((B1:B100=J1)*(C1:C100=J2)*(D1...

v4 client install
I'm having a problem trying to install the v4 CRM client to my desktop P= C. The machine runs Windows XP SP2, and Office 2003 SP3. Everytime I run the CRM client install, the following error occurs: ---------------------- Length cannot be less than zero Parameter name: length ---------------------- Previously this machine had the v3 laptop client installed. I've re-attempted the install several times. The error occurs whether I = = choose the standard or offline client. I've performed the steps at = http://www.microsoft.com/dynamics/crm/using/deploy/removeclient.mspx to = ...

Lookup problem in Excel 2003
I had a formula in an Excel workbook called Payments to update the bank balance during 2011-2012: =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2011-2012\Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F) This worked perfectly well until I needed a similar formula for 2012-2013. I have amended it as appropriate but it only returns 0.00 on my Payments sheet. This is the formula I am using: =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2012-2013\Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F) Can anyone see where I have gone wron...

How to change the number zero to a zero with a slash through it ?
How do I change the zero to a zero with a slash through it. Will this work for all Office programs (i.e., Word, Access, Power Point, etc.) Thanks ! You could try. Start> Run > charmap. Then select Courier New. There is a O with slash throught it there. Copy it and paste into application � VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56225 "Bubey" <nneldred@...

Lookup Formula
I'm not sure if this would be a look up formula, but is there a formula where I can type in a number in one cell and in the cell next to it, it will pop up a description that i've written in another cell connected to that number on another sheet? EXCEL 2007 Please refer to:- http://www.pierrefondes.com/ Item number 93. In Sheet1 in cells A2 to A6 entry of any one of these numbers:- 200 / 400 / 500 / 600 - will return, respectively, any one of:- 60 / 10 / 10 / 20 - in cells B2 to B6. The, "conversion table" is found on Sheet2. Just ...

Create a lookup field in CRM3.0
Can I create a lookup field in Quote and this field is referenced to another entity(eg. user in CRM system)? ---I mean: this field is a lookup field, the picklist is all user in CRM system. Is CRM3.0 support is function? Can anyone can help me? Thanks -- Richard CRM 3.0 does not support creating new relationships (or their lookups) between system entities so the answer is no, sorry.. Peter Lynch "Richard Jin" <RichardJin@discussions.microsoft.com> wrote in message news:E6041321-457B-4F32-9620-AF688FABC96C@microsoft.com... > Can I create a lookup field in Quote a...

copying value from lookup into textfield
Hi, I found some posts to this question here in the forum and some in the internet, but most of them belong to CRM 3.0 and some use methods I not allowed or unable to do |-) So my questions are: Are there any Addons for this? Is there a method, just using javascript within the webclient? (I am not able to programm any callout or webservice) best regards Antonio It's an easy script. Assume your fields are named new_text and new_lookup, then use the following: crmForm.all.new_text.DataValue = crmForm.all.new_lookup.DataValue ? crmForm.all.new_lookup.DataValue[0].name : null; Or in ...

data lookup
Here is my problem, I have a database with an assortment of data in a that takes place on many multiple days, and on each day i have multilple entries, and this database grows everyday, i.e. Date Time Amount category 1/1/10 1:00 x y 1/1/10 2:00 x y 1/1/10 3:00 x y .. .. .. 1/31/10 1:00 x y 1/31/10 2:00 x y etc. What i want to do is have a worksheet that corresponds to each individual days worth of data. So when i put a date in the corresponding cell at the top of the any ne...

Unit lookup in order
I am using CRM 1.2 and have the integration to Great Plains installed. When I select a product for an order there is a required lookup called Unit. There are no values in this field. If you look at the field properties the description says 'Unique identifier for the unit that is associated with the order product.' Where is it pulling the values from? How do I get this field populated? Thanks, Tricia ...

Trouble printing zipcodes that begin with zero
I have a list of Names, addresses and zip codes for some reason the zip codes that begin with a 0 do no print. can anyone help me? Hi Richard, try : Format|Cells>Custom and in the field Type, type: "0"0 (in this case, the zip will start allways with a zero), or try to format the cell as Text Regards. -- JRod http://EXCELer.blogspot.com "Richard Mahan Sr" <dmachen35@home.com> escreveu na mensagem news:OXLdg95yFHA.2072@TK2MSFTNGP14.phx.gbl... > I have a list of Names, addresses and zip codes for some reason the > zip codes that begin with a ...

Formula help--lookup?
I have various items in a spreadsheet. Some of these items are repeated in various rows, but have a different text description for it. They all have the same reference number, just a different description. I want to combine all the descriptions into one cell. I am doing vlookup, but it is only putting in the first occurance. Is there a way to combine all descriptions into one cell? Yes, I like to use COUNTIF to set up a sequential number for each multiple of the unique reference. However, your description of your data is a bit vague, so I can't really recommend a particula...

to lookup data for 'today()'
e.g A B C D 1 Date (From) 01 Jan 2009 01 Feb 2009 01 Mar 2009 2 Date (To) 31 Jan 2009 28 Feb 2009 31 Mar 2009 3 Actual Qty 205 217 300 4 Target Qty 180 250 401 if today = 15 Feb 2009, how to get actual qty using excel formula? Hi, =SUMPRODUCT(--(TODAY()>=A1:H1),--(TODAY()<=A2:H2),A3:H3) "nordiyu" wrote: > e.g > >...

eliminating zero-value rows
Greetings, A series of rows comprise a product list we are using (eg "Item X, Item Y, Item Z"). A column next to these products is used to designate the quantity of each item we will need for a particular job. So, we might put a "3" in the column next to Item X, or a "0" next to Item Y. I would like to then have a tab that lists all the materials we need for a job, and their quantities, without having the blank rows if the quantity is zero. Any ideas how to accomplish this? Thanks for your help. Cheers, Scot B. I'd keep all my data in place an...

range equal zero then "x"
If I wanted to check to see if all cells in a range are blank how would I do that. I want to put an "x" in the formula cell if all cells in the range are zero. thank you very much! Jeff =IF(COUNTBLANK(Range)=ROWS(Range),"x","not blank") -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "jzachar" <jzachar@discussions.microsoft.com> wrote in message news:CE64CF49-BF03-4CE0-B4B5-98C52...

Null values
Hi, I'm new to the Access database query language & need some help regarding "null values" & converting them into another record within the Access database. The question & background is:- I have extracted some payroll data from another database (excel) & have copied it into an Access table (payroll table). One of the fields in the table, "Tasks" contains records of payroll tasks & in that field there are a number of null records. What I want to do is have a new updated "Task" field within the table which takes all null records & updates...

Sum for Non Zero
I have a text box on a form called it_est_total. The control source right now is =[development]+[middle_apps]+[b2b]+ [data_warehouse]+[qa]+[other]+[prod_support_ba]+[prod_support]+[ivr_hours] This is fine if all text boxes have a number entered. I do not have the default set to 0 on all of these. They are all blank. I need to keep it this way. However, I want the text box it_est_total, to total even if all the fields are not entered. Can someone help with this code? Thank you. -- Message posted via http://www.accessmonster.com =Nz([development],0)+Nz([middle_apps],0)+Nz([b2b],0)+Nz...

=Countif (not zero)
Hello All, Using Excel XP. I have the following sample worksheet: A -------------------- 1 17 2 6 3 20 4 0 5 11 6 0 ----------------- 7 2 (=countif(A1:A6,"<15", ??? I want to count the values in A1:A6 if they are less than 15 but not counting any zero's. In A7 I would want the result to be 2. Any help writing the formula would be appreciated. Mike Try these: =COUNTIF(A1:A6,"<15")-COUNTIF(A1:A6,0) =SUMPRODUCT((A1:A6>0)*(A1:A6<15)) ...

Lookup a value in a table and display text if a value exists
I have a customer database form named frmMain with a field called txtAgency. I also have a table that has a column of agency codes that are in the Outlook Global directory. The table is called tbl_Global and consist of about 40 records. If the user inputs an agency that is within this table on the frmMain, I want the phone number field (txtPhone) on the database form to automatically display "See Outlook". If the agency is not within this list, I want the end user to be able input a value on the form. What is the best way to go about this? Use DLookup() to get t...

I need to always Round UP if greater than zero at all. HOW?
I am in office 2007 Access DB - I have quantities that are greater than Zero but the Round function will round down to 0, if the number is not over .5 - how do I get it to ALWAYS round UP to the next nearest whole number even if it's .00001? Try this -- IIF(Int([YourField])<[YourField], Int([YourField])+1 , [YourField]) -- Build a little, test a little. "RP" wrote: > I am in office 2007 Access DB - > I have quantities that are greater than Zero but the Round function will > round down to 0, if the number is not over .5 - how do I get it to ...

!! XSD Bug !!
While converting a schema library into a .Net class library using XSD I constantly kept running into the following error message issues by the tool: Error: Error generating classes for schema 'XYZ'. - Schema with targetNamespace='' has invalid syntax. - Invalid 'memberTypes' attribute: Value cannot be null. Parameter name: name. The schema library being so extensively large, it took me 4 days to discover that the error was caused by one of many simpleType definitions in one of many includes. An xs:union definition included an additional space at the end of its mem...