That old #VALUE! problem

I have made up a spreadsheet that has lots of cells with IF formulas but I 
want the result of those cells to add up as a total. However, no matter how 
I try, the total cell keeps coming out with the dreaded #VALUE!.

All the IF formulas are fine and work well but I need them all to add up but 
show nothing in the cell unless there is a value worked out in the original 
IF cells.

Hope that's clear and that someone can help.

I am using OFFICE2000.

Nick 


0
nick5661 (9)
4/12/2006 3:56:33 PM
excel 39879 articles. 2 followers. Follow

4 Replies
634 Views

Similar Articles

[PageSpeed] 26

What exactly is your formula?
If you don't show us the formula that creates the problem we can't identify 
the problem,

Anyone in tis group is willing to help.

Greets,

Sybolt 


0
4/12/2006 6:04:17 PM
Hi Nick,

Have you tried the formula auditor to find the source of the error? 
Generally that error is from trying to sum text.

-- 
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"Nick Wakeham" <nick@dr5.co.uk> wrote in message 
news:443d1feb$0$10741$9a6e19ea@news.newshosting.com...
>I have made up a spreadsheet that has lots of cells with IF formulas but I 
>want the result of those cells to add up as a total. However, no matter how 
>I try, the total cell keeps coming out with the dreaded #VALUE!.
>
> All the IF formulas are fine and work well but I need them all to add up 
> but show nothing in the cell unless there is a value worked out in the 
> original IF cells.
>
> Hope that's clear and that someone can help.
>
> I am using OFFICE2000.
>
> Nick
> 


0
firefytrNO (70)
4/12/2006 7:00:51 PM
I suspect you have something like this in your IF statements:

=IF(condition,"1","0")

Although this will look as if you are returning numbers to the cells,
they are in fact text values. If you then try to carry out some
arithmetic on them the arithmetic function will return #VALUE.

Hope this helps.

Pete

0
pashurst (2576)
4/12/2006 7:10:05 PM
Thank you - that is a huge help and has explained a lot!

Many thanks

Nick

"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:O2f2IwkXGHA.3740@TK2MSFTNGP03.phx.gbl...
> The value error derives from calculating text so in your if formulas you
> might have included either "" or "5" as an example. Also when totaling if
> you use SUM(A1,A3 etc) or =SUM(B1:B10) they will ignore text and only sum
> the numerical values. OTOH =A1+A3 as an example will cause an error if a
> cell included in the calculation is text
>
>
> -- 
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Nick Wakeham" <nick@dr5.co.uk> wrote in message
> news:443d1fc4$0$1214$9a6e19ea@news.newshosting.com...
>>I have made up a spreadsheet that has lots of cells with IF formulas but I
>>want the result of those cells to add up as a total. However, no matter
>>how I try, the total cell keeps coming out with the dreaded #VALUE!.
>>
>> All the IF formulas are fine and work well but I need them all to add up
>> but show nothing in the cell unless there is a value worked out in the
>> original IF cells.
>>
>> Hope that's clear and that someone can help.
>>
>> I am using OFFICE2000.
>>
>> Nick
>>
>>
>
>


"Pete_UK" <pashurst@auditel.net> wrote in message 
news:1144869005.248775.220750@i39g2000cwa.googlegroups.com...
>I suspect you have something like this in your IF statements:
>
> =IF(condition,"1","0")
>
> Although this will look as if you are returning numbers to the cells,
> they are in fact text values. If you then try to carry out some
> arithmetic on them the arithmetic function will return #VALUE.
>
> Hope this helps.
>
> Pete
> 


0
nick5661 (9)
4/13/2006 1:02:28 PM
Reply:

Similar Artilces:

Xml Attribute..id problem??
Hi, I have an xml file as below <root> <table id =1> <user>abc</user> <age>25</age> </table> <table id = 2> <user>xyz</user> <age>45</age> </table> ..... ..... </root> What I want is this id attribute to increment automatically. So I declared it of type "ID" ..but how do i access the value of this attribute. When i add a record it gets added to the xml file but the id attribute does not show..So how do i refer to this attribute. smita wrote: > What I want is this id attribute to incre...

Files in folder Windows.old in Vista
I have installed Ultimate on a Business version and then received the Windows.old folder. I want to delete it but have found same working files in old path than in the new. It appears that Windows updates the working files in both folders. Are they mirrored or copies. I changed the name of one file in old - corresponding file in the new folder received the same name. If I delete files from Windows.old will they be deleted from current folder too? "HBj" <HBj@discussions.microsoft.com> wrote in message news:12D90C20-D766-48D2-AA9C-D0FF7097DEBA@microsoft.com... ...

Conditional Sum Problem
I have three columns of data something like this: A B C 1 Balance Report Product Type 2 3,000 Y Oranges 3 1,000 N Oranges 4 4,000 N Apples 5 500 Y Pears 6 2,500 N Pears 7 8,000 Y Oranges Range A2:A7 is named "Balance", B2:B7 is named "Report" and C2:C7 is named "Product_Type". I am trying the following: =SUMPRODUCT((Balance)*(Product_Type="Oranges")*(Report="...

select * from #temp problem in HQ
Hello I have a client who Every time he tries to enter he has the following error: "name of the the object #temp not valid select # from #temp where [status] in (0,1,2,3,4,5,6,7)" According to him he didn't do anything unusual just locate the server outside the office.. any suggestions? Thks in advance for your time This is a multi-part message in MIME format. ------=_NextPart_000_0192_01C94979.AAC35BD0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Rod, Search for any QS*.tmp files on the hard driv...

Finding the low value of a range of values
Hi i have the formula below to find the high value(columnC) between tw times as shown in the 1 hour table at the bottom. How would i need to modify this formula to search for the LO value(column D),just swapping MAX to MIN and altering the references t the C column to D is not enough,what else do i need to do? =SUMPRODUCT(MAX(($A$3:$A$1000>=M3)*($A$3:$A$1000<=N3)*($C$3:$C$1000))) 5 MIN TIME OPEN HIGH LOW CLOSE 15:50 1.8394 1.8396 1.8393 1.8396 15:45 1.8396 1.8396 1.8393 1.8394 15:40 1.8389 1.8396 1.8389 1.8396 15:35 1.8389 1.8391 1.8389 1.8389 15:30 1.8385 1.8393 1.8385 1.8389 15:2...

Problems when Closing Outlook
Everytime I open outlook and close it, it still stays open in the task manager. So if I open up Outlook ten times in one day, i'll have ten versions of Outlook opened in the task manager. It won't let me log out of my computer without having all of them shut down. Any help would be appreciated. Thanks Josh Are you running any software that integrates with Outlook? WinFAX, virusscanners and synchronisation software are known to keep Outlook open. For more info see; http://www.howto-outlook.com/faq/outlookdoesntclose.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com T...

Opening docs from old versions
In attempting to open a doc created in an ancient version of Word (for Windows, not DOS), I get this message: "You are attempting to open a file that was created in an earlier version of Microsoft Office. This file type is blocked from opening by your registry policy setting." What is the registry setting I must change to override this and allow the opening? Thanks. -TedMi Hi Ted, I'm pretty sure this is the KB article you need: http://support.microsoft.com/kb/938810 There are both downloadable reg files and instructions for manual editing...

How to get the value of the prompt environment variable
I need a way to get the value of the %prompt% environment variable. I would prefer it actually if I could get the value from a more reliable source as this is a console app I'm writing. -- ClassicVB Users Regroup! comp.lang.basic.visual.misc Free usenet access at http://www.eternal-september.org Leo formulated on Wednesday : > I need a way to get the value of the %prompt% environment variable. I would > prefer it actually if I could get the value from a more reliable source as > this is a console app I'm writing. Scrap the more reliable way section as i...

Hosted MS CRM SignIn ISA Problem
Hi, when I internal login to "https://company1.crm123.com" then I can normally logon to signin.aspx and will be forwarded to loader.aspx But I cant logon external via our ISA Server to that website. The signin.aspx is loaded but after the authentication is checked the signin.aspx will displayed again. The forwarding to the loader.aspx page does not take place. We have already tried all possible settings on the isa servers. Has anybody an idea to solve the problem? Many thanks Best regards Kachte hi, did anybody find any solution for this in the meantime? thanks in advance, fritz...

Keyboard Shortcut for "Paste Values"?
Hello Please help - the keyboard shortcut for "Paste" is "CTRL-V". What is the shortcut for "Paste Values" [i.e., the equivalent using the mouse to click "Paste - Paste Special - Values - Ok"?] Thank you!! I do not know one but there is a button for it. Right click toolbar, customize, Commands, Edit, Paste Values (icon) "Sam" <Sam@discussions.microsoft.com> wrote in message news:78D10DD9-385F-4B9F-BEC7-82A4CCB3CD4E@microsoft.com... > Hello > > Please help - the keyboard shortcut for "Paste" is > "CTRL-...

Office 2001 Printing problems with appletalk
once upon a time before i upgraded to OS 10.2.4 for my powerbook G3... I could use My Office applications with as many printers as my PowerPrint program would find. Now a days, i'm printerless because of this one issue. I can not activate appletalk... in Classic (Chooser) which is where my MSOffice resides. I understand that i have to activate appletalk In the OSX systems pref /networking pannel... (and i've even tried creating my own network just for printers) But i've had no luck. (it wont reconize USB connections for some reason) I have even just purchased a brand new printer wi...

office 2007 and old office 2003 excel files
I have a desktop pc with Office 2003 and Vista Home premium system. I am getting a new laptop and am purchasing office 2007 home student to install on it. With the laptop I can access shared files from the desktop. However, all my old files were generated by office 2003. Will the office 2007 be able to access and process these old files? Also, can I uninstall office 2003 from my desktop and install the office 2007 on it and use the old files created by office 2003? Thanks. Carl Yes, Excel 2007 can read 2003 files... You can either convert them to the new version by savi...

OWA problem #25
Dear all, Dose anyone know can I reinstall only OWA in Exchange 2003? http://support.microsoft.com/kb/320202/en-us Step 1-5 "Leo" wrote: > Dear all, > > Dose anyone know can I reinstall only OWA in Exchange 2003? Thanks, I will try "Ray" wrote: > http://support.microsoft.com/kb/320202/en-us > > Step 1-5 > > "Leo" wrote: > > > Dear all, > > > > Dose anyone know can I reinstall only OWA in Exchange 2003? ...

importing a cell value
I need to import a cell values from an un-opened .xls using a variable address ie cell A34 = the location of the .xls on our server (j:\121213\Drafting\Quants\) "105 White Ant" = the name of the .xls B3 = the cell I need to import C6 = value(A34+"105 White Ant"+B3) Any help or advice would be hugely appriciated Sorry I should also have specified that I want to avoid the use of macros. "Kristian G" wrote: > I need to import a cell values from an un-opened .xls using a variable address > ie > cell A34 = the location of the .xls on our ...

Calculating Commisions based on a range of values
I am trying to calculate a commision that is based on a sales price. The example commision is a fixed fee for 1- 225000, 225001-275000, 275001-40000, .... For what ever reason I have been unable to figure out how to accomplish this. Ideally I can write a formula that checks the sales price and determines the appropriate commision. So Sales Price Comm $225,000 $2,000 $300,000 $2,500 $450,000 $4,000 Any advice and help is greatly appreciated!! Thanks to all you excel wizards for your help.... Dianna, Build a small table somthing like 1 1000 2250...

Retrieving/opening Outlook express emails from old drive
Hi all, I recently needed to replace the hard drive in my notebook. The old drive is still operational, so I can now using it as an external hard disc. How can I open to read / and save or import all the email folders in Outlook express from this older disc? I'd like to save all those messages to a backup/archive. Thanks, Shayne. Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You can also find some good Out...

Lookup value in a table from a different worksheet
Hello What is the formula for Lookup value in a table which is in different worksheet? thank you Hi, Without a description of what your trying to do all that can be offered is general help and I suggets this. Start your formula manually with =lookup( and then navigate to the lookup value (say) a1 of sheet 1 and Excel will correctly input the syntax of =lookup(Sheet1!A1 manualy enter the comma and then navigate again to the lookup vector and once again Excel will enter the correct syntax for that range. Carry on until the formula is complete -- Mike When co...

Problem when running Exchange Calendar Update Tool
I ran the Exchange update tool against a few mailboxes, and I am noticing a problem. The tool appeared to work to some extent, but one of the mailboxes I ran it against has some items modified and some not modified. Two recurring meetings that this user created and invited other people to were modified properly and updates were sent out automatically to the other people in the meeting. However, two other recurring meetings that he initiated and invited other people to were not modified and no update messages were sent for those meetings. Appointments that he created just for himself...

old posts
How can I find a post I made yesterday? Hi, when you open this post, click on your name, it will pop up a window with your information, at the to right click on Recent posts by this user if this helps please click yes, thanks "Eric" wrote: > How can I find a post I made yesterday? I'd use google. Give it enough information (keywords/author/subject): http://groups.google.com/advanced_group_search http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100 Ron de Bruin has an excel addin that you may like: http://www.rondebruin.nl/Google.htm Eric wrote:...

Queue Email problem
Hi All, I have a weired problem. There is a queue in my CRM implementation. It is getting emails properly. but what ever email come to this queue gets forwared to it self with only the subject and no body. so each email gets a duplicate without body and FW: added in subject. There is no callout or workflow to send email automatically to itself. There are other users and queues which are functioning properly. Only one queue is showing this weired problem. I have removed and deployed rule from Rule Deployment Wizard to confirm if that part is ok. I have no clue. Any quidence will be app...

How To find identical values
Hi I have a sheet with in which i like to find identical values and report in how many times does this value appear. A B C D x y z x =3 r x r y =1 f f z z =2 r =1 f =2 I have tried several functions but no luck, can anyone tell me how to do this? Thanks in advance marcgijs...@hotmail.com wrote: > Hi > I have a sheet with in which i like to find identical values and report > in how many times does this value appear. > A B C D > x y z x =3 > r x r y =1 > f f z z =2 > r =1 &g...

Problems printing templates in Excel XP
I will try and get as much detail about my problem in here. I have two pcs at my work that are not printing the text from a worksheet created in a previous version of excel (97 I think), Others with the exact same load have no problem printing these same worksheets. The typed in txt on these templates will appear in print preview but once they print it notihng is there, it looks like the blank template. I have tried saving it in Excel 97 & 5.0 95 format, that did not help. I have uninstalled & reinstalled Office XP and installed Off2k with no luck either. I have tried refo...

Extract similar values
I have a worksheet named "Height" where my Column A consists o different persons' height measurement for example, 123, 178 etc. I another worksheet named "Summary", let's say in cell A1 I keyed-in 182 Are there any formula that allow me to retrieve or check my "Height worksheet for all cells that consist of 1, 8 and 2? For example, 182 821, 218, 128 etc.? Thanks -- Message posted from http://www.ExcelForum.com You could use the advanced filter, assume that the header is in A1 and the data starts in A2, in an empty cell preferably in row 2 somewhere put =AND...

Inventory qty problem
We recently completed a physical inventory for a vendor and committed. While checking a specific item, we found that the item quantity in properties was reporting a minus 3 qty when it should have been zero. When viewing the same item in the item movement report, it shows the expected zero qyt. I reindexed the files in administrator but that didn't correct the problem. Any suggestions would be greatly appreciated. Thanks. John -- John G at the Tradewinds Shops This is a multi-part message in MIME format. ------=_NextPart_000_0031_01C6D657.27DF6B10 Content-Type: text/plain; charse...

problems with automatic debit/direct debit
My Money is not allowing me to use automatic debit like I was used to using with my other bank. Is this a bank problem or do I not have something configured right? Michael Before someone can give a useful answer, you'll probably need to elaborate a bit on what you mean by "is not allow me to use automatic debit". -- Chris Cowles Gainesville, FL "mpmorin" <mpmorin@verizon.net> wrote in message news:4Apde.13057$KP.2990@trndny02... > My Money is not allowing me to use automatic debit like I was used to > using with my other bank. Is this a bank p...