#Value Error #3

Hi
I'm using this formula 
=IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2
and coping it down a column and as long as column E is not null it returns 
the value and if column E is null I get the #VALUE Error. Can this be told to 
return blank if colunm E has a null vallue? The formula is written in column 
I.
0
RayG (15)
11/17/2004 1:53:07 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
590 Views

Similar Articles

[PageSpeed] 39

"RayG" <RayG@discussions.microsoft.com> wrote in message
news:A1AD5C13-D52C-424B-B574-22293BE72049@microsoft.com...
>
=IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$
29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2

> if column E is null I get the #VALUE Error. Can this be told to
> return blank if colunm E has a null vallue? The formula is written in
column I

Untested, but try

=IF(E2="","",(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calcul
ations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2)

Ian


0
me1 (409)
11/17/2004 2:29:44 PM
received error message (the formula you typed contains an error

"IC" wrote:

> 
> "RayG" <RayG@discussions.microsoft.com> wrote in message
> news:A1AD5C13-D52C-424B-B574-22293BE72049@microsoft.com...
> >
> =IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$
> 29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2
> 
> > if column E is null I get the #VALUE Error. Can this be told to
> > return blank if colunm E has a null vallue? The formula is written in
> column I
> 
> Untested, but try
> 
> =IF(E2="","",(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calcul
> ations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2)
> 
> Ian
> 
> 
> 
0
RayG (15)
11/17/2004 5:06:08 PM
I think IC forgot the if:

=IF(E2="","",IF(COUNTA(G2:H2)=2,INDEX(calculations!$A$29:$P$45,
                   MATCH(G2,calculations!$A$29:$A$45,0),
                     MATCH(H2,calculations!$A$29:$P$29,0)),"")*E2)



RayG wrote:
> 
> received error message (the formula you typed contains an error
> 
> "IC" wrote:
> 
> >
> > "RayG" <RayG@discussions.microsoft.com> wrote in message
> > news:A1AD5C13-D52C-424B-B574-22293BE72049@microsoft.com...
> > >
> > =IF(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calculations!$A$
> > 29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2
> >
> > > if column E is null I get the #VALUE Error. Can this be told to
> > > return blank if colunm E has a null vallue? The formula is written in
> > column I
> >
> > Untested, but try
> >
> > =IF(E2="","",(COUNTA(G2:H2)=2,INDEX(Calculations!$A$29:$P$45,MATCH(G2,Calcul
> > ations!$A$29:$A$45,0),MATCH(H2,Calculations!$A$29:$P$29,0)),"")*E2)
> >
> > Ian
> >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
11/17/2004 11:49:47 PM
"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:419BE39B.C3FE36FA@netscapeXSPAM.com...
> I think IC forgot the if:
>
> =IF(E2="","",IF(COUNTA(G2:H2)=2,INDEX(calculations!$A$29:$P$45,
>                    MATCH(G2,calculations!$A$29:$A$45,0),
>                      MATCH(H2,calculations!$A$29:$P$29,0)),"")*E2)

Oops! Sorry :-)

Ian


0
me1 (409)
11/21/2004 3:04:59 PM
Reply:

Similar Artilces:

Spell check error in OWA 2003
Have OWA on Exchange 2003, getting the following error UNLESS we logon with domain\username: The spelling in this item can't be checked. Try again later. It also appears to "time you out" so you have to log back on again if you attempt to do a spell check. Tried the following KB articles, and still no go. http://support.microsoft.com/kb/820378 http://support.microsoft.com/default.aspx?scid=kb;EN-US;825430 Our OWA is behind an ISA 2004 server (reverse proxy). Any ideas? ...

Error message opening Outlook after removing Panda antivirus
I am using Outlook 2002 as part of Office XP Pro. I used to run Panda antivirus on my pc but have switched to Norton now that Panda has expired. I had config Panda to scan mails whenever Outlook opens. However now that Panda had been removed, I always get an error message to the effect that Panda cannot be found. How can I get rid of this association to avoid having this annoying message evrytime I open Outlook? Thanks. You may have to manually remove Panda from your add-in manager. Go to Tools/Options/Other/Advanced Options/Add- in Manager/ and then make sure that if Panda is listed ...

Application returns ras error 678 No answer under virtual PC 2004
Hi I hope that i have posted this question to the correct group, if not i am sorry. I have an application that connects to the Internet using RAS. In the real world there are many copies of the program running without issue. Under Virtual PC 2004 SP1(Windows 98 and others) the program fails to connect with error 678 No Answer very early on during the modem negotiation phase. However Using dial up networking the modem can establish a connection. I was hoping to be able to use Virtual PC to perform the majority of the testing with this application. As such i would be very greatful if ...

MSMoney 2007
In MSMoney 2007 Deluxe, When I go to an Income Vs Spending report, I have transactions that don't have assigned categories. When I click on the 'Assign categories' I get a Microsoft Visual C++ Runtime Library - Runtime Error. This happens every time, and on two different computers. Looking at the detailed technical information I see an 0x40000015 out of mscoree.dll. Anyone have any idea how to fix this, or where to get additional help from Microsoft? Thanks Phillip In microsoft.public.money, Phillip wrote: >In MSMoney 2007 Deluxe, When I go to an Income Vs Spending re...

sending vcards results in you do not have permission error.
When sending vcards inside or outside of our environment, users are getting a : You do not have permission to send to this recipient. For assistance, contact your system administrator. Anyone know what might be causing this? What antivirus do you use? At what level? "Paul Fogle" <pfogle@unknowncompany.net> a �crit dans le message de news: 543d5288e6794ff2ac3d4daf0206348d@ureader.com... > When sending vcards inside or outside of our environment, users are > getting > a : > > > You do not have permission to send to this recipient. For assistance, >...

Trying to truncate or separate the first 3 characters/digits of co
I have a spreadsheet with a column of numbers. I'm trying to extract / truncate / remove the leading 3 digits in each cell. The numbers vary in length, so using the LEFT or RIGHT function doesn't seem to be an option. The numbers represent division / dept. numbers within my company. The leading 3 digits represent the division, which I want to remove / discard. =RIGHT(TEXT(A1,"0"),LEN(TEXT(A1,"0"))-3) -- Kind regards, Niek Otten "Jim" <Jim@discussions.microsoft.com> wrote in message news:9393C07F-6B74-4CCA-A1EB-E0A1B9622535@microsoft.com...

424 Object Required Error !
Hi, The below code manages to find if the inputted row is a duplicate of a previous record and deletes it sucessfully but the VBA compiler gives an error message as shown on the subject of this message... Can someone correct the code? '-------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column >= 3 And Target.Column <= 5 Then ' 3 to 5 stand for C to E columns For i = 1 To Target.Row - 1 If (Cells(i, 3) = Cells(Target.Row, 3)) And (Cells(i, 4) = Cells(Target.Row, 4)) And (Cells(i, 5) = Cells(Target.Row, 5)) Th...

How can I limit the range of data graphed by the value of another column?
The question is hard to ask, which is why I haven't found the answer. The spreadsheet has lots of data, but I want to graph a subset of the data at a time. One column contains an integer that is incremented every 700-1000 rows (ie 1..1,2..2,etc.) which is the event number, and there are 2 other columns of data that I want to plot. So I want to plot one chart with all the data with the event number of 1-3, another 4-6, etc. I know I can just specify the data range with the row numbers that correspond to the event number range I want, but is there a way excel can do the work for me? I a...

Host Unreachable error
Folks, I have one domain that I cannot send email to. Email sits in the queue until it times out. The only error is "Host Unreachable" yet I can ping and trace the site... Any ideas on where to look? Thanks, Ed If you host your own dns - try deleting the domain's dns records that is coming up unreachable. I had the same problem on NT 4.0 with exchange 5.5. Then restart you IMS. ...

error code 939523078
My exchange server has crashed and i am unable to restore (PANIC) what do error codes 939523078 and 3355444218 denote and how do i go about solving this problem ? John, The errors appear to be coming from the backup program. If I am correct you will need to talk to your backup program vendor to help troubleshoot these errors. From the disaster recovery aspect there are many different disaster recovery options for you even if you do not have a restored database. You did not specify which version of Exchange you are using, but here are some articles to get you started: Exchange 5.5: 185...

Outlook error message #16
I get the following message when I try to setup user with existing mailbox. Microsoft Outlook: Unable to display the selected foler or item. You do not have permission to log on ...

Error with stdev array
I'm trying to use a stdev array to calculate the stdev for rows within a list. The formula references a template spreadsheet where it looks for IDs in column A that match the unique value in A# of the current spreadsheet. The values that I want the stdev of are located in column AO of the template = STDEV(IF(('[Averaging Data Template.xlsx]Template'!$A$1:$A$1000=A10)*('[Averaging Data Template.xlsx]Template'!$AO$1:$AO$1000<>""),'[Averaging Data Template.xlsx]Template'!$AO$1:$AO$1000)) What is odd is that I'm getting a different value for stdev...

Outlook 2000 attachments #3
All of a sudden, messages I receive from web sites that I have subscribed to started coming with attachments. The attachments are a .txt file that contains the exact same message as the message itself. Since this only happens in Outlook and not when I retrieve the very same messages in my MSN account, it has to be something going on within Outlook. I have checked all of my settings and nothing appears to be amiss. It just started about 2 days ago and I can't figure out why. Anyone have any ideas as to what is going on? If you have had Outlook open for a really long time (week...

Creating contacts gives error
We have a user who is trying to create a contact and getting an error says "Error occurred. Please contact system administrator" when saving the record. When I look at the contact list, it creates the record. But the error message annoying the user when creating contacts. The user has Business Unit access for Contacts (Create, Read, Write Delete Append, ApptendTo, Assign & Share) Any suggestions. Thanks. ...

Error in sum function when using merged cells
Help! I recently discovered an error in my shipping worksheet. sometimes need to merge certain cells when I am combining items fro different purchase orders into the same shipment. However, when totaled the column (SUM) containing the merged cells, it delivers wrong answer. Specifically column F and column H that contain the merged cells don' add up correctly. Column F is +1 more than it should be. Column H i +125 more than it should be. I have been using this feature for about 6 months, and this is th first time it did this. Any ideas why the error +----------------------...

Vertical text, font corruption #3
Hi there. I'm trying to draw a vertical text. But I see the font is corrupted. I mean it looks ugly. No antialiasing and so. whe I draw the text horizontal it looks just fine. I'm using "lf.lfEscapement = - 90 * 10;" to make a font vertical and "pDC->TextOut(20, 22, sTitle);" for drawing. lf.lfQuality = ANTIALIASED_QUALITY for both vertical and horizontal. Have now idea what it could be... Any ideas? Thanks. ...

Trigger error
I have a delete trigger that is trying to get information during the delete using the deleted object and I am getting the errors below. The multi-part identifier "deleted.RepCompanyID" could not be bound. The multi-part identifier "deleted.BrandID" could not be bound. The trigger code giving the error is below. p.s. This is from an old database so alternatives in constraint use would be ok. Thanks. DECLARE @RepCompany varchar(200); DECLARE @Brand nvarchar(50); DECLARE @OldInfo varchar(200); SET @RepCompany = (SELECT RepCompany FROM dbo.Rep...

showing data values on chart
in excel when mouse stand on chart,excel is showing the data values of that point.how can to do this action in access? Exactly the same. When you hover the mouse over a data point for a little amount of time, a yellow bow should come up describing what's in your data point. -- Regards, Steve "afshin" wrote: > in excel when mouse stand on chart,excel is showing the data values of that > point.how can to do this action in access? Where you insert chart?i insert chart on report,and i can't to see data value on data point. "Steve"...

on error msg box help
The following code will return a message box whether or not an error results from running the macro... what gives? Sub test() On Error GoTo errr Range("a2").Value = Range("A1") / Range("B1") errr: MsgBox "error", vbOKOnly, Error End Sub You have to do something else if no error. Exiting is an option. Sub test() On Error GoTo errr Range("a2").Value = Range("A1") / Range("B1") Exit Sub errr: MsgBox "error", vbOKOnly, Error End Sub Gord Dibben MS Excel MVP On Wed, 18 Nov 200...

Runtime error! "Abnormal program termination" in Outlook
Suddenly I get a dialogue box (Microsoft Visual C++ Runtime Library) saying:- Runtime error! "Abnormal program termination" Tried the MS Ofice disc to 'Repair' but with no success! Suggestions, please (NB All Virus protection, fire wall & MS downloads are up to date) >Suddenly I get a dialogue box (Microsoft Visual C++ Runtime Library) >saying:- Runtime error! >"Abnormal program termination" >Tried the MS Ofice disc to 'Repair' but with no success! >Suggestions, please > >(NB All Virus protection, fire wall & MS downloads ...

Nav 740 4.3-Inch Widescreen Portable GPS Navigator
Price:$329.99 Image: http://thediscountlocator.info/image.php?id=B000UHPVES Best deal: http://thediscountlocator.info/index.php?id=B000UHPVES I bought this when it was available for under $100 in order to test out what could be done with it as a Windows CE device. It's Nav capabilities seem reasonable compared to my Garmin Nuvi. The real value was in the ability to usurp it's Auto Navigation capabilities and use it for installing XCSoar onto as a flight computer. Not something your typical user is going to do, but V7 didn't do anything to prevent that. Nice plus in my bo...

0x80040119 error code
Hello, We cannot have the Offline adresse book. I reinstalled Exchange Server 2003. No *.ost or *.pst problem. I rebuild several time the offline address book Thanks for your help, Cyril "Cyril Vannier" <cyrilvannier@hotmail.com> wrote in news:eyIsCcj#FHA.1032 @TK2MSFTNGP11.phx.gbl: > We cannot have the Offline adresse book. > I reinstalled Exchange Server 2003. > No *.ost or *.pst problem. > I rebuild several time the offline address book What is the specific problem you are encountering? What service pack are you using? Regards, -- Arlo Clizer FAQ: htt...

run time error 10-22-03
I am having a lot of trouble when I open up word I get run time error 52 in VB. I have tried uninstalling word and reinstalling it. WE have tried deleting the macro but still to no avail can someone help me please? ...

Updating current value
when using an asset account in Money 2006 I can update the current value of my house. This posts a record for the increase which must be categorized or it shows in reports as unknown. Trouble is, it is not income and a loss would not be an expense. so what category should I use for this type of transaction so it is reflected in my net worth reports but not as a monthly income or expense. -- Thanks from Canada It may not be taxable income, but if it isn't income, what is it? We only have three other choices in this kind of accounting: Expense, Asset, Liability. "Gerald from ...

Quick Campaign Bulk Email Error
Hi When I try to generate 600 emails using a Quick Campaign I receive the error code 0x8004023b, and no activities are generated. I know there is not a limit on the number of emails I can send out because I have been previously successful in sending out 700 emails. Any ideas? Sounds like an invalid type is being assigned. Try to perform the same Quick Campaign task first using just one or two of the accounts. See if you generate the same error. If you don't then the problem might be in one or more of the entities you are selecting, which may have an invalid ID? If you do gene...