Default numeric value

I am creating a spreadsheet dealing with dollars.  There are several formulas 
that I have created using percentages.  One particular formula multiplies a 
percent in one cell by the $ value in another.  If I have no $ value in the 
cell the formula uses a '1' instead of what I would expect '0'.  How can I 
set up my sheet so that if a cell is blank the default value will be '0'?
0
Thomson (4)
11/10/2005 9:11:21 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
619 Views

Similar Articles

[PageSpeed] 42

Would help to see your formula, but:
=if(isblank(a1),"",a1*b1)
************
Anne Troy
VBA Project Manager
www.OfficeArticles.com

"thomson" <thomson@discussions.microsoft.com> wrote in message 
news:79DEA08B-9C53-403F-8645-85AD0A042376@microsoft.com...
>I am creating a spreadsheet dealing with dollars.  There are several 
>formulas
> that I have created using percentages.  One particular formula multiplies 
> a
> percent in one cell by the $ value in another.  If I have no $ value in 
> the
> cell the formula uses a '1' instead of what I would expect '0'.  How can I
> set up my sheet so that if a cell is blank the default value will be '0'? 


0
ng1 (1444)
11/10/2005 9:34:57 PM
This works great thanks!  Here is what the formula ended up being ....  
=SUM(IF(ISBLANK(D9),0,D$1*D9),IF(ISBLANK(E9),0,E$1*E9),IF(ISBLANK(F9),0,F$1*F9),IF(ISBLANK(G9),0,G$1*G9),IF(ISBLANK(H9),0,H$1*H9))

I still have the question on why a 'blank' would end up being evaluated as a 
'1' when you find a product.  When the formula is a 'SUM' the blank is 
considered a '0' ... so why is it different if it it s product?  Maybe 
someone from Microsoft can tell me??





"Anne Troy" wrote:

> Would help to see your formula, but:
> =if(isblank(a1),"",a1*b1)
> ************
> Anne Troy
> VBA Project Manager
> www.OfficeArticles.com
> 
> "thomson" <thomson@discussions.microsoft.com> wrote in message 
> news:79DEA08B-9C53-403F-8645-85AD0A042376@microsoft.com...
> >I am creating a spreadsheet dealing with dollars.  There are several 
> >formulas
> > that I have created using percentages.  One particular formula multiplies 
> > a
> > percent in one cell by the $ value in another.  If I have no $ value in 
> > the
> > cell the formula uses a '1' instead of what I would expect '0'.  How can I
> > set up my sheet so that if a cell is blank the default value will be '0'? 
> 
> 
> 
0
Thomson (4)
11/10/2005 10:32:04 PM
You can use an array formula to make it shorter. Enter:

=SUM(IF(ISBLANK(D9:H9),0,D$1:H$1*D9:H9))

To make it an array formula, press ctrl+shift+enter to enter the formula as 
opposed to pressing enter alone.


"thomson" wrote:

> This works great thanks!  Here is what the formula ended up being ....  
> =SUM(IF(ISBLANK(D9),0,D$1*D9),IF(ISBLANK(E9),0,E$1*E9),IF(ISBLANK(F9),0,F$1*F9),IF(ISBLANK(G9),0,G$1*G9),IF(ISBLANK(H9),0,H$1*H9))
> 
> I still have the question on why a 'blank' would end up being evaluated as a 
> '1' when you find a product.  When the formula is a 'SUM' the blank is 
> considered a '0' ... so why is it different if it it s product?  Maybe 
> someone from Microsoft can tell me??
> 
> 
> 
> 
> 
> "Anne Troy" wrote:
> 
> > Would help to see your formula, but:
> > =if(isblank(a1),"",a1*b1)
> > ************
> > Anne Troy
> > VBA Project Manager
> > www.OfficeArticles.com
> > 
> > "thomson" <thomson@discussions.microsoft.com> wrote in message 
> > news:79DEA08B-9C53-403F-8645-85AD0A042376@microsoft.com...
> > >I am creating a spreadsheet dealing with dollars.  There are several 
> > >formulas
> > > that I have created using percentages.  One particular formula multiplies 
> > > a
> > > percent in one cell by the $ value in another.  If I have no $ value in 
> > > the
> > > cell the formula uses a '1' instead of what I would expect '0'.  How can I
> > > set up my sheet so that if a cell is blank the default value will be '0'? 
> > 
> > 
> > 
0
Clement (5)
11/10/2005 11:17:02 PM
I tried this and it works well.  I am new to using Excel ... very powerful.

"Jason Clement" wrote:

> You can use an array formula to make it shorter. Enter:
> 
> =SUM(IF(ISBLANK(D9:H9),0,D$1:H$1*D9:H9))
> 
> To make it an array formula, press ctrl+shift+enter to enter the formula as 
> opposed to pressing enter alone.
> 
> 
> "thomson" wrote:
> 
> > This works great thanks!  Here is what the formula ended up being ....  
> > =SUM(IF(ISBLANK(D9),0,D$1*D9),IF(ISBLANK(E9),0,E$1*E9),IF(ISBLANK(F9),0,F$1*F9),IF(ISBLANK(G9),0,G$1*G9),IF(ISBLANK(H9),0,H$1*H9))
> > 
> > I still have the question on why a 'blank' would end up being evaluated as a 
> > '1' when you find a product.  When the formula is a 'SUM' the blank is 
> > considered a '0' ... so why is it different if it it s product?  Maybe 
> > someone from Microsoft can tell me??
> > 
> > 
> > 
> > 
> > 
> > "Anne Troy" wrote:
> > 
> > > Would help to see your formula, but:
> > > =if(isblank(a1),"",a1*b1)
> > > ************
> > > Anne Troy
> > > VBA Project Manager
> > > www.OfficeArticles.com
> > > 
> > > "thomson" <thomson@discussions.microsoft.com> wrote in message 
> > > news:79DEA08B-9C53-403F-8645-85AD0A042376@microsoft.com...
> > > >I am creating a spreadsheet dealing with dollars.  There are several 
> > > >formulas
> > > > that I have created using percentages.  One particular formula multiplies 
> > > > a
> > > > percent in one cell by the $ value in another.  If I have no $ value in 
> > > > the
> > > > cell the formula uses a '1' instead of what I would expect '0'.  How can I
> > > > set up my sheet so that if a cell is blank the default value will be '0'? 
> > > 
> > > 
> > > 
0
Thomson (4)
11/11/2005 5:19:05 PM
Reply:

Similar Artilces:

Change Outlook Default Setting United States of America at end of address line (Country/Region)
How can I turn off or omit the words "United States of America" which is added as the last line by the Contacts app when I use an address from Outlook Contacts to address an envelope in Word? This is a waste of ink. Outlook 2002 SP2 A typical address would look like this when clicked from the Outlook Contacts book to the envelope app in Word. Want to know how to change the default setting, turn off the last line, (don't want it included in the address) which is added by Outlook as a default line. Want the default to be without country/region. Example of what is completed by curre...

default font size for comments
Is there a way to change the default font size for comments inserted into an Excel worksheet? It defaults to Tahoma 8 pt, which is too small, and is not the font I want to use. Windows>start>settings>control panel>display or right click desktop>properties then appearance and advanced (in XP), scroll find tooltips. That is where it can be changed Obviously it will affect more than comments -- Regards, Peo Sjoblom "dan yntema" <anonymous@discussions.microsoft.com> wrote in message news:5b0301c40095$2d750f70$a001280a@phx.gbl... > Is there a way to chan...

Default Email Account (outlook 2003)
My outlook 2003 is currently set up to have a microsoft exchange account and a pop3 account. The exchange account is used for the shared tasks, internal email (within office), shared calendars/contacts and so on. The POP3 account is our actual external email account which is used to send/receive with people outside of our office. The Microsoft exchange account is highlighted as 'default' however we would prefer that the POP3 account be default (much easier in order to send external messages). If I go into my email accounts settings and select POP3 and assign it default it will...

How do I change file/open/"files of type" to default to "all file.
How do I change file/open/"files of type" to default to "all files(*.*)" thanks I'm not sure you can change this permanently. I just type *.* in the filename box and hit enter (it's almost almost as quick as a default <bg>.) How do I change"files of type" default wrote: > > How do I change file/open/"files of type" to default to "all files(*.*)" > > thanks -- Dave Peterson ...

Overlapping of values in different tables.
I am wondering if there is a way to create a pivot table - or some other way - that will show the relationships of rows between tables. I have attached data from potentially 4 different tables and the business is wanting to see the number of times that a paticular ID shows up in different table join combinations. For example, we want to know the count of unique IDs will show in the NPS, AAC and ECM tables. Is it possible to create a pivot table that will dynamically adjust based on a user choosing the column combinations that they want to see without having to create a separate pivot...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Retrieving default values specified from XML Schema file
Hi All, I have an VS.NET 2003 app which will be processing XML files, some of which will have empty elements (for example, <Item />). I have an XSD file which should supply default values, but I am not able to read these plugged in value using an XMLDocument object. The validation is working fine--it's just not providing any default values. Has anybody tried this? Am I must be missing something? This is one of those that "should be simple..." Here's a simplifed version of the code and XML files: XmlDocument xmldoc = new XmlDocument(); XmlSchemaCollection my...

multi-value field
I'm still learning Access, so I must apologize in advance for my question. I have four tables, tblBrochure, tblStaff, tblOrganization, tblResourceDistribution. tblBrochure has two fields -BrochureName -Active (y/n) tblStaff has two fields -StaffName -Active (y/n) tblOrganization has two fields -OrgName -textbox (0 and 1, explained below) tblResourceDistribution has 5 fields -DistribDate -StaffName (lookup from tblStaff ->Staffname -Organization (lookup from tblOrganization ->OrgName) ...

Problem with draging a formula,one cell value fixed,trivial question
Hi. I have a value in cell A10. Then i have a formula in cell C1 which goes: (A1+B1)/A10. If I drag the formula down to cell C5 the formula for C2 will be (A2+B2)/A11, for C3 will be (A3+B3)/A12,....,for C5 will be (A5+B)/A14. I would like the formula to have the A10 parameter fixed so when i drag the formula down the A10 would always be A10(it wouldn't go up by 1). So the formula for C2 will be (A2+B2)/A10, for C3 will be (A3+B3)/A10,....,for C5 will be (A5+B5)/A10. Please help. Thanks, Marko make your original in C1 formula =(A1+B1)/A$10 then drag down. See help on absolute versus re...

Changing default DCOM permissions
I finally got my problem with the Trend Micro WFBS console on an SBS 2003 fixed. I had to follow steps 1-3 from Microsoft KB article 319624, method 2, reproduced below. I was reluctant to do this, because the article is for Windows 2000 Server, but it seems to have worked. I am queasy, though, because I checked another SBS 2003, and the default impersonation level is set to "Impersonate," just as it was on the problem server before I made the change that fixed the WFBS console. What are the ramifications of this change? What am I sacrificing? I just am not com...

Lookup value selection
Is there any way to select lookup value by pressing "Enter" key on keyboard and avoid mouse click? -- romeo!! Please clarify. I could use the up and down arrow to select the values and then press "Enter" key to select the lookup value - works for me in v4.0. Frank Lee, Microsoft Dynamics CRM MVP http://www.workopia.com/Links.htm http://microsoft-crm.spaces.live.com "romeo" wrote: > Is there any way to select lookup value by pressing "Enter" key on keyboard > and avoid mouse click? > > -- > romeo!! ...

Vlookup
Dear all, For vlookup, it will return the value of the destination cell. Is it possible to return the row no. instead of value of the destination cell?? Your help is highly appreciated. Thanks & regards, Automne Look at the MATCH function in Help. On Sat, 5 Mar 2005 01:41:39 +0800, "automne" <hersbt_no_spam@yahoo.com.hk> wrote: >Dear all, > >For vlookup, it will return the value of the destination cell. Is it >possible to return the row no. instead of value of the destination cell?? > >Your help is highly appreciated. > >Thanks & regards,...

Can you set a default to always include a footer containing the pa
I would like for all of my saved Excel documents to include a footer that has the path, file and sheet name. I would like for it to automatically do this. I seem to often forget. Is there a default setting for this? ...

Set default values
I've set up a template where I have headers and cell validations. I want to be able to have some of the fileds in the row autopopulate once the first cell in the row is entered. Do you know of a way that I can code this using VBA. For example, A3 is populated with a value, I then want G3 to be updated with a "1". -- Message posted via http://www.officekb.com In G3: =IF(A3="","",1) -- Gary''s Student - gsnu2007k In G3 use the formula =IF(A3<>"",1,"") The "" is a pair of double-quotes with no space between be...

isv.config rollback to default value when publish new customization
dear expert, Anybody had idea for this problem?? "msnews.microsoft.com" <hattha9@hotmail.com> wrote in message news:eSkdJp7UFHA.3312@TK2MSFTNGP09.phx.gbl... > dear expert, > Anybody had idea for this problem?? > > > Yes, there is another copy of the ISV.config that gets copied on public - cant remember where it is. A search will find it, rename it and all will be fine for the next publish. I will try and find the exact location and post The file should be located in the c:\Program Files\Microsoft CRM\Tools\Config, or wherever you installed M...

Sending value to a cell
Hi there, I want to dump a computed value into a cell that isn't the one the calculation was done in. Can it be done from the original cell? Hi no, not with a formula -- Regards Frank Kabel Frankfurt, Germany "CM32134" <CM32134@discussions.microsoft.com> schrieb im Newsbeitrag news:0768C79B-586C-42CB-A7AE-1A817C4CE5F5@microsoft.com... > Hi there, > I want to dump a computed value into a cell that isn't the one the > calculation was done in. Can it be done from the original cell? A worksheet formula returns a value and can do nothing else. Think...

How to ignore records with a duplicate ID based on a value
I hae searched the forum tono avail, so I'll ask for help. I have a table [Scope Event Table] which records events with status changes. The table uses an auto numbered primary key (not shown in the example below). It has data like this: DR_ID Event_Date Reason 12556 01/03/2008 Added 12556 01/24/2008 Closed 12874 01/05/2008 Added 14128 02/09/2008 Added I am a novice at SQL. I am trying to build a query that would pull only the DR_ID’s where the most recent Reason = “Added”. So I would want results to look like this: DR_ID Event_Date Reason 12874 01/05/2008 Added 14128 02/09/2008...

Exchange 2k7 powershell truncates multi-value properties
Greetings, I'm running the following command: Get-SendConnector -Identity "Send Connector Name" | ft -autosize -wrap -property AddressSpaces We have over 20 domains in this list. When I run this command it lists 16 of these domains and then just sticks ... at the end. If I leave out the -wrap switch the output lists only 3 domains. How can I get this command to list ALL the contents of the AddressSpaces property? Thanks Try to pipe to format-list instead. --- Shay Levy Windows PowerShell MVP http://blogs.microsoft.co.il/blogs/ScriptFanatic...

How to round the numeric value?
Kindly tell me that how can we round the numeric value? Example: 1.2 = 1.5, 1.7 = 2.0 "Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com... > Kindly tell me that how can we round the numeric value? > Example: 1.2 = 1.5, 1.7 = 2.0 Use the RND function..... =CEILING(A1,0.5) -- Regards Dave Hawley www.ozgrid.com "Salman Saeed" <Salman Saeed@discussions.microsoft.com> wrote in message news:F5460D85-6DEE-4EC5-8E83-A1A5E158966A@microsoft.com... > Kindly...

vlookup-Closest value
Dear Friends, How can I use Vlookup to give me the closest value greather than or equal to vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 It chooses 5.1 and give me the corresponding value. Thank you, atatari wrote: > Dear Friends, > > How can I use Vlookup to give me the closest value greather than or equal to > vlookup value.For example if my lookup value is 5 and I have 4.9 and 5.1,5.2 > It chooses 5.1 and give me the corresponding value. > > Thank you, VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) The default behavi...

The most occurence of a value in a column
I have 1 to 5 as values in a column. How can I find the number with the most occurrence in that column? Thanks, cpliu Hi =MODE(A1:A100) -- Regards Frank Kabel Frankfurt, Germany "cpliu" <chanciusliuDeleteThis@yahoo.com> schrieb im Newsbeitrag news:Xns959C8C47C309BchanciusliuDeleteThi@130.133.1.4... > I have 1 to 5 as values in a column. How can I find the number with the > most occurrence in that column? > > Thanks, > > cpliu Try: =INDEX(A1:A5,MATCH(MAX(COUNTIF(A1:A5,A1:A5)),COUNTIF (A1:A5,A1:A5),0)) Array-entered, meaning press ctrl/shift/enter. H...

I am making a charts with weird X-values for my Ecology cl\
i am making a chart with weird X-values. Instead of single, whole numbers, I need each value to be as follows: 1-2, 3-5, 6-10, 11-15, etc. Please help me!! My chart keeps going to whole numbers instead and its been driving me crazy for days! What chart type are you using? If you want your X-axis to be category labels, rather than actual numbers, you may find that a line chart (or one of the other chart types) is more suitable than an XY (scatter) chart, if the latter is what you are currently trying. -- David Biddulph "Holly" <Holly@discussions.microsoft.com> wro...

reformulating question regarding maximum value
I had an hour ago put a question up named "find maximum", but I had not formulated my query fully, hence this query. In each row, I want to find the second highest value in that row, and then as output give the value of that cell in the first row. For example: 1 2 6 8 6 5 4 3 2 4 5 6 3 2 3 8 6 4 5 3 7 2 3 1 The second largest value in the second row is nr 6. It is in the fourht cell of that row. The fourth cell of the first row is the value 8. This should be the output for the second row. The second largest value in the third row is the number 6. It is in the first cell of th...

Automatic formatting of minimum/maximum value in a range.
I want that if I select a range of numbers the minimum or maximum should be selected by a single command How about cond. formatting? 1. Select the range. 2. Go to Format > Conditional Formatting 3. Select "Formula Is" and copy in this: =OR(INDIRECT("rc",0)=MAX(rng),INDIRECT("rc",0)=MIN(rng)) where "rng" is a defined name for your range. HTH Jason Atlanta, GA >-----Original Message----- >I want that if I select a range of numbers the minimum or maximum should be >selected by a single command >. > What do you want to do with t...

merging a value in a text field with each line in a memo field
I am trying to merge a value in a text field with the information in a memo field using a query to create a report. This is the query I am using: SELECT AVRelay+","+IPRange FROM T_Sites AVRelay is the text field and IPRange is the Memo. IPRange has multiple lines, each of which I need appended with the value in AVRelay for the report. It should look like. 2,192.168.1.0/24 2,192.168.0.0/24 2,192.168.2.0/24 Instead it looks like 2,192.168.1.0/24 192.168.0.0/24 192.168.2.0/24 I have searched far and wide but has come up empty. Please help! I would gues...