Help With Problem: Nested IF Function, Office2K v Office2003

Hello Group,

I'm looking for some help with a nested IF worksheet function. Shown
below is a formula that I'm using in a model.

=SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0))

If short, what I'm trying to do is scan the data in two columns and for
any cells which meet the TRUE case in both columns, then sum the third
numbers for all cases returning TRUE-TRUE. If either cell returns a
FALSE, then enter 0.

I created the original formulas and model in Office2000 and have used
the model for more than a year with no problems. Now, I'm trying to
rework the model on a new PC using Office2003. When I try to modify the
formula, I'm getting the #VALUE! error returned.

When I try to troubleshoot using the Evaluate Forumla tool, it returns
a #VALUE! error on the first worksheet look up, as if the forumla is
unable to find the worksheet.

I'm stumped! Anyone have any ideas? Is there a simpler way to do this
formula, such as using VLOOKUP?

0
stevehegg
12/11/2005 3:28:43 PM
excel 39879 articles. 2 followers. Follow

3 Replies
511 Views

Similar Articles

[PageSpeed] 27

It is an array formula, so after adding the formula, you should commit with
Ctrl-Shift-Enter.

It can also be achieved with
=SUMPRODUCT(--(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),--(worksheet!$B$3:
$B$54="<90"),worksheet!$C$3:$C$54,0)

which is NOT an array formula

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

<stevehegg@3cushion.com> wrote in message
news:1134314923.313627.163720@g49g2000cwa.googlegroups.com...
> Hello Group,
>
> I'm looking for some help with a nested IF worksheet function. Shown
> below is a formula that I'm using in a model.
>
>
=SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="
<90",worksheet!$C$3:$C$54,0),0))
>
> If short, what I'm trying to do is scan the data in two columns and for
> any cells which meet the TRUE case in both columns, then sum the third
> numbers for all cases returning TRUE-TRUE. If either cell returns a
> FALSE, then enter 0.
>
> I created the original formulas and model in Office2000 and have used
> the model for more than a year with no problems. Now, I'm trying to
> rework the model on a new PC using Office2003. When I try to modify the
> formula, I'm getting the #VALUE! error returned.
>
> When I try to troubleshoot using the Evaluate Forumla tool, it returns
> a #VALUE! error on the first worksheet look up, as if the forumla is
> unable to find the worksheet.
>
> I'm stumped! Anyone have any ideas? Is there a simpler way to do this
> formula, such as using VLOOKUP?
>


0
bob.phillips1 (6510)
12/11/2005 4:33:02 PM
On 11 Dec 2005 07:28:43 -0800, stevehegg@3cushion.com wrote:

>Hello Group,
>
>I'm looking for some help with a nested IF worksheet function. Shown
>below is a formula that I'm using in a model.
>
>=SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0))
>
>If short, what I'm trying to do is scan the data in two columns and for
>any cells which meet the TRUE case in both columns, then sum the third
>numbers for all cases returning TRUE-TRUE. If either cell returns a
>FALSE, then enter 0.
>
>I created the original formulas and model in Office2000 and have used
>the model for more than a year with no problems. Now, I'm trying to
>rework the model on a new PC using Office2003. When I try to modify the
>formula, I'm getting the #VALUE! error returned.
>
>When I try to troubleshoot using the Evaluate Forumla tool, it returns
>a #VALUE! error on the first worksheet look up, as if the forumla is
>unable to find the worksheet.
>
>I'm stumped! Anyone have any ideas? Is there a simpler way to do this
>formula, such as using VLOOKUP?

Probably you did not enter your formula as an ARRAY formula (holding down
<ctrl><shift> while hitting <enter>).

You could also write it as the **array** formula:

=IF(AND(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),worksheet!$B$3:$B$54="<90"),worksheet!$C$3:$C$54,0)


--ron
0
ronrosenfeld (3122)
12/11/2005 4:38:12 PM
Ron Rosenfeld wrote:

> Probably you did not enter your formula as an ARRAY formula (holding down
> <ctrl><shift> while hitting <enter>).

Thanks Bob & Ron .... it's been so long since I originally created the
model, that I forgot about that important step in the formula entry.
That solved the problem. Cheers!

0
stevehegg
12/11/2005 5:10:59 PM
Reply:

Similar Artilces:

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

*major* functional issue with MOSS/WSS?
We are looking to move a big site over to using MOSS in a big as an alternative to shared network drives (ugh). One of the biggest issues is sending docs as attachments via email for others to edit. We'd like to get everyone to use the 'send a link' functionality from thew Sharepoint DL BUT when the user receives the email and clinks on the embedded link the doc opens read only. They then cant edit and save their changes. Seems a big an counter intuitive functional flaw. Are we missing something here? Is there any easy (for the end user) way to send those links to a doc ...

Problem with CFTPConnection
Hi I've an application that connects to an FTP Server and uploads a big directory (size over 500 MB). Now what happens is that after sometime the application stops uploading files and stays idle. The FTP server connection timeout value is set to 2 minutes. So the server seeing the connection idle for two seconds, disconnect the session and no more file is uploaded. On debugging I found out that after some time CFTPConnection.PutFile() method hangs on some random file and stays like that for 5 minutes (default send timeout value of CFTPSession). During this time the server connection timeo...

Here is fix for Windows file problems
If you have installed a Windows update and started having problems opening, closing, copying files, see: http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&mid=1fe358b1-c8eb-4217-ad2a-4b9fcf741916 A better solution is offered at http://support.microsoft.com/kb/918165 "Problems in Windows Explorer or the Windows shell after you install security update MS06-015". Patricia Shannon wrote: > If you have installed a Windows update and started having problems opening, > closing, copying files, see: > > http://ww...

GP GL recurring entry problem!
hi, I have created the prepayment batch which will recur 11 times monthly in general ledger, this expenditure occurred starting from Aug, been posted on Nov..however, after posting it, there is just Aug expenditure bumped up while same journal no. showed two entries, which are duplicated and one of that is showing the audit trial code, the other one remains original journal no. and batch no.; also, the other prepayment batch i created recurring 3 months, it did show the aug and sep expenditures, however the oct expenditure can't be showed up after post. Linking with this two ...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Problems migrating users from E2K to E2K03
Having problems when I try to move a mailbox over to Exchange 2003 server from a Exchange 2000 SP3 everything seems to go fine other than the fact that now when sending e-mail I receive "The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address." I have no problems when I create a mailbox for a user on the E2k03 box, but when it comes up to moving a maibox it does not seem to go as smooth. Stumped Al Hi Al, It sounds like some of the attributes for the user...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

CRM Outlook Client Configuration Wizard Problem
CRM 4.0 - Microsoft Outlook Client Guys, We've Microsoft CRM 4.0 Server roll up 8 as well as CRM Outlook Client on the same roll up version. Our Internal Web Access works without problems users get to it and they're automatically authenticated. IFD Development is the same way except they're prompt through a login page for their username and password when they're coming from an external network. Ever since we moved to a new facility users are experiencing problems connecting to CRM using their outlook client, I have a test machine where I have the CRM Outlook Client ins...

Sequencing Office 2010 no-beta version with App-V
Hi, I have the final version of the Office 2010 and I tried to sequence it with App-V. To the beta version Microsoft published a deployment kit to do it, but I don't find the deployment kit to the final version. Does Microsoft have a date to publish it? Does somebody has found it? Best regards Don't worry :) Microsoft has published the Deploment Key today, http://blogs.technet.com/appv/archive/2010/05/06/the-microsoft-office-2010-deployment-kit-for-app-v-is-now-available.aspx Thanks. ...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

request to solve my problem #5
hello friends this is vijay,i got a problem while doing my project based on vc++ (i.e)"How to implement Fit Window feature in VC++6.0 in an MDI Application" if any of you got any idea about that please do reply me with the proceedure.i will be waiting for your reply bye with regards vijayprasadreddy ...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Excel List Sorting Problem (Descending)
Hi there, I'm having trouble sorting my list--my column contains *only* 4-digit numbers but when I click on "descending order", only about the first half of the rows are arranged this way, before it begins again to arrange the rest in descending order. Like this: 5120 5119 5118 4000 3050 5116 4112 etc. Has this problem happened for anybody else? I'd appreciate any help you can offer. Part of your list is text, although it looks like numbers. Format an empty cell as number. Enter the number 1. Copy. Select your "numbers". Edit>Paste Special, check Mul...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...