2003/2007 Formula issues

Hi,

First time poster here.

I have the following formula in 2007 which works fine. However a number of people here at work still use 2003 so I am experiencing some compatibility issues.

=IFERROR(AVERAGEIF(Data!$AN:$AN,Tables!$H$49:$H$111,Data!$AE:$AE),NA())

I have tried a number of times to replicate it in 2003 but I cant seem to get it to work. If I had hair I would be pulling it out over this one.

Any help would be greatly appreciated

Thanks

Darren


0
3/20/2012 9:12:20 AM
excel 39879 articles. 2 followers. Follow

1 Replies
1756 Views

Similar Articles

[PageSpeed] 5

"Darren Goddard" <darren.goddard@virginmedia.co.uk> wrote:
> a number of people here at work still use 2003 so I am
> experiencing some compatibility issues.
> =IFERROR(AVERAGEIF(Data!$AN:$AN,Tables!$H$49:$H$111,Data!$AE:$AE),NA())

=IF(COUNTIF(Tables!$H$49:$H$111,Data!$AE:$AE)=0, NA(),
SUMIF(Tables!$H$49:$H$111,Data!$AE:$AE)
/ COUNTIF(Tables!$H$49:$H$111,Data!$AE:$AE))

or this array-entered formula (press ctrl+shift+Enter instead of just 
Enter):

=IF(COUNTIF(Tables!$H$49:$H$111,Data!$AE:$AE)=0, NA(),
AVERAGE(IF(Tables!$H$49:$H$111=Data!$AE:$AE,Tables!$H$49:$H$111))) 

0
joeu20041 (141)
3/20/2012 4:20:42 PM
Reply:

Similar Artilces:

Help: Formula put into entire row creates large file size
Hi, I have a spreadsheet where I would like one column to be the concatenation of two other columns. For example, in column C I would have =A1&B1. This excel file will be given to people and they will fill in values into columns A and B. The trick is, I don't know how many rows there will be, so I don't know how many rows in column C should have this formula. If I paste this formula into the entire column C, the excel file blows up to 3 megabytes. (It's only 15KB without this column). Are there ways for me to accomplish this more efficiently? Thanks for any help Are y...

Percent issues in 2007
We just converted to Office 2007, so that could be my issue. Here is my problem: I have a field called "Percent Discount" with a field size set to Single and no decimal places. On theform, the user types in 10% into the field (2007 automatically enters the % sign). The next field is an expression =[Amount]-[Dollar Discount]-[Percent Discount] (The user can give a dollar discount or a percent discount depending on their needs). It is only subtracting 1% rather than 10%. How do I get it to both show 10% for the user and subtract the 10% from the amount? On Feb 25, 11:19=A0am, MuzicK....

Formulas in Excel
Hi I am not sure how to word this but here goes, I would like to be able to enter a w-4 withholding exemption (single and 1 or married and 2, etc.) and have excel calculate this change automatically. Would this be an IF formula? Thanks for any help you can offer. -- Life, Love and Happiness Give us some figures or a bit for information as anything can be an IF statement, a simple =A1+B1 can be If=(A1<>"",A1+B1,"") or an if and If=(And(A1<>"",B1<>""),A1+B1,"") etc. so as you can see we need a little more info! -- The...

Exchange 2003 Tracking Center
Im running Exchange 2003 and when I search for emails with the message tracking center they pull up but the subject line is left blank on all searches done. Do I have something miss configured that is causing the subject lines to be left blank? Thanks blusporttrac you have to enable "subject logging and display"...I do not believe it is enable by default... <blusporttrac@yahoo.com> wrote in message news:1117119115.669602.209960@g49g2000cwa.googlegroups.com... > Im running Exchange 2003 and when I search for emails with the > message tracking center they pull up...

Outlook 2003 Wont Remember Password
Hello All, Since upgrading from Office XP to Office 2003, Outlook now does not remember our POP server passwords for any user on our Terminal Server network. We download mail from a Linux POP server for company email, but Outlook will always pause at the download screen and ask for the password, no matter whether we click the 'save this password in your password list' option box or not. Can anyone assist? Thanks, Devan ...

Corrupt Access 2003 File
I was working on this file earlier today, I even just opened it and it was fine and all of a sudden there is a write delay to the save the file, I closed it and I try opening it again and it is corrupted. I don't get it. The message I am getting is below. What does that mean. I just finished making a lost of update to the file. The last time I backed it up was Tuesday, and I have done a lot of work to it between then and now. I don't have the time to go back and redo all that work again. Any help will be appreciated. "Microsoft Office Access has detected corruption in this ...

Yes/No Format Issue
Hi Groupies I have been building databases for years and have not run into this problem before. This 2007 database I am working in has a few yes/no fields. When I add these fields to a Form, they are show up as textboxes instead of the yes/no check box I am expecting. They respond correctly in queries, autoreports and autoforms but when I add it manually, I get Yes or No and have to manually change it to a Check Box Any ideas? Anybody else having the same issue? -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself,...

Adding old clipart from previous Publishers to Publisher 2003
Hi I installed a new Publisher - 2003 - on my computer. I had kept all my old disks from Office 2000 with all the old clipart. However, the new Publisher doesn't seem to have as nice a clipart gallery as my old version had, and I had wanted to add all my clips to the newer version. Is this possible in a way that Publisher will *automatically* setup all the old clips so that I can easily search for the exact picture I am looking for, instead of having to go through each clip manually by the very boring names such as clip1, clip2, clip3 etc. (or however the clipart is installed)? I want...

Outlook 2003 won't let me delete msg in Outbox
I have an email in my outbox that has a 6 MB attachment. Well, for whatever reason, Outlook was having issues sending the email, so after several hours, I decided to delete it. Pressing the delete button does nothing, no error. Trying to permenantly delete it (shift delete) and it gives me the error message "The item could not be deleted. It was either moved or already deleted, or access was denied." It's a bizarre error. I'm only using Outlook as a pop3 email reader. There's no exchange on the backend. This is with Windows XP Professional. Any help woul...

Connect to Exchange 2003 from a different domain
Hi!! Sorry, I don't speak english well :-) This is my network: domain 1: NT and XP Clients using Outlook domain 2: Exchange 2003 Each client use LMHOST to view Exchange server. I can connect with it and the first time I use Outlook I need to write: username, password and domain. I write the username,... from Exchange domain and Outlook works fine. So, from time to time, Outlook opens a new window to write this parameters. It's a nightmare!!! I haven't a trust relationship from domain 1 to domain 2, and I haven't configured mailboxes, it use default parameters. I tried to...

Mailboxes in Exchange 2003 Enterprise Server
I have implemented a policy to limit the mailbox size to 30000 K and implemented a 21 day Age Limit. However, 21 days has passed and the 30000 K limit has passed on more than 50% of our mailboxes. How can we enforce this policy to work properly and clean out the mailboxes to bring this down to 30 K. The policy I have created is running and reporting is enabled.It is configured for report only, but I would like this to change to "Move to deleted items folder". However, since nothing seems to happen as of yet...I am hesitant to make the change until I can see a change and tha...

Outlook 2003 #5
I just got Outlook 2003 in the mail yesterday. Is there anyone out their who has the latest version of Outlook yet? DaveS wrote: > I just got Outlook 2003 in the mail yesterday. Is there > anyone out their who has the latest version of Outlook > yet? Why do you ask, Dave? Is there something wrong? If so, please let us know the problem...maybe we can help you -- Donald L McDaniel Post All replies to the Newsgroup, so that all may be informed ============================================================= ...

PST not created in Outlook 2003 after importprf
Hi all, I would like to create a Exchange Server Outlook profile with a Unicode or ANSI PST file. When I launch outlook 2003 with the -importprf option, the creation of the profile work fine (exchange server settings) but none PST is add (if the file exist) or create and add (if the file not exist). When I try my outlook.prf file with the -importprf option on an outlook 2007, all work fine, in the same environment (domain, policy settings, etc...) I use the Outlook ADM file to force the cached offline mode as default. I try to use the settings "Exchange Unicode Mode - Ignore Arc...

Outlook 2003 Storing Sent Items with IMAP
Hi, I've recently started using Outlook 2003, (upgrading from Outllook Express). I have several IMAP accounts and with Outllook Express whenever I sent an email from one account, it automatically stored it in the IMAP folder Sent Items of the account I sent it from. How can you do this in Outllook 2003?? It seems to store everything in Sent Items which means I can't read the mail I've sent from other locations. Regards. "Dougie Mac" <dougiemac> wrote: >Hi, > >I've recently started using Outlook 2003, (upgrading from Outllook Express). >...

Publisher 2007 #31
If I use Autoflow in text boxes, I cannot get the test to wrap round pictures, they just sit on top! What am I doing Wrong? In Publisher 2000 it would be, Click on the text box; select Format, check "Wrap text around objects" Option. OK -- Don - Publisher 2000� Vancouver, USA "richwoo" <richwoo@discussions.microsoft.com> wrote in message news:F176A1BE-6E72-48FB-98F9-573C15E677CC@microsoft.com... > If I use Autoflow in text boxes, I cannot get the test to wrap round > pictures, they just sit on top! > What am I doing Wrong? > ...

Using Powerpoint 2007
PPS files on webpage open as PPT using PowerPoint 2007. Don't have this same issue when opening the same link from an Office 2003 computer, they run the show only. -- Miroshak ...

integration with Outlook 2003: slower on XP than W2K? after upgrade?
Hi, I have a VB.NET (2.0) application that integrates with Outlook 2003. I have windows XP SP2. The users had Windows 2000, and Office 2000. They upgraded (!) to Office 2003. But: - an action that takes 0,5 seconds on my pc, takes them 4-5 seconds - an action that takes 2,5 seconds on my pc, takes them 8-9 seconds So for a test we completely desinstalled Office 2003, and did a full re-install of 2003 on a system. Performance increased with +- 30%: times are now 2,5 seconds and 6 seconds. But still much more than my 0,5 and 2,5.... What's the reason of this performance-problem? The ...

How do I create custom postcards in Word 2007?
I want to create my own postcards using the right size and type of paper for mailing. This depends entirely on your printer. Can it handle the heavy weight of card used in postcards, and can it handle the unusual size of a postcard? On Apr 18, 2:54=A0pm, Create Custom Postcards <Create Custom Postca...@discussions.microsoft.com> wrote: > I want to create my own postcards using the right size and type of paper = for > mailing. Perhaps the easiest way to start is to get some Avery (or similar) postcard stock, which will have two postcards per sheet. Then find the a...

MSN BillPay and Money 2007 #2
Has anyone had any experience with setting up MSN BillPay in Microsoft Money 2007? From Managing Online Services within Money 2007 I signed up for MSN BillPay and have my user name and password to make payments but limited to using the MSN Web Site. When I try to enter this info in Money 2007 to activate MSN billpay online services I get a message saying that I must have "special sign-in information" other than the web based user name/password and points me back to the MSN Web Site for the needed info. My problem is that I cannot find any mention of this special sign-in on any of t...

Formula appears instead of value
What would cause a formula to display instead of the result. For example in sheet1 A1 if I enter =Sheet2!A1 I will get =Sheet2!A1 displayed in the cell. I should point out this problem does not occur if I select a different range on the same sheet. Hope you can understand what my problem is! Pat Most likely the cell is formatted as Text. Change the format to General, and press F2 then Enter. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pat" <glass_patrick@hotmail.com> wrote in message news:ejkP1YMBFHA.1452@TK2...

the system cannot find the file specified outlook 2003 #2
Attachments show up in emails, but when you try to open them, the messages comes up "the system cannot find the file specified", but other recipiants on same email can open theirs. ...

Copying a worksheet and retaining values (not formulas)
I have a comprehensive sales spreadsheet setup which reads raw data from my PC, collates and calculates, and displays a summary sheet. This summary sheet is fine as long as it is on my PC, but sending it to others who do not share the source data means all the links are broken. Rather than give access to the links, I want to create a file containing the actual calculated values, layout and formatting, for sending to others. Can this be done? (and if so, how!) Hi 1. way Right-click on sheet tab, select 'Move or Copy', check 'Create a Copy' and select 'New book'...

meetings issue
We have 2 users, User1 and User2. On AD, User1's "Send on behalf" has User2 added. The problem is when another individual sends a meeting invite to both User1 and User2, only User1 receives the invite but not User2. Any suggestions on why this is happening? thanks. On Wed, 30 Dec 2009 10:06:01 -0800, wli2k2 <wli2k2@discussions.microsoft.com> wrote: >We have 2 users, User1 and User2. > >On AD, User1's "Send on behalf" has User2 added. > >The problem is when another individual sends a meeting invite to both User1 >and ...

Export Outlook 2007 Calendar to HTML
When I export Outlook 2007 Calendar to HTML, "save as web page", only the first appointment of any day is shown in the HTML version of the Calendar. Can I show more appointments? Am I doing somthing wrong? is there an alternate template I can use? Thanks Grant@cgs ...

Outlook 2003 Can't Open Item MS OFFICE FAILED
Yikes, I'm having a real problem. I am running Windows XP with Service Pack 2 Home Edition Office 2003 with Service Pack 2 (only have Outlook and Word installed) Toshiba Notebook M15-S405 Satellite Pro 1.4 ghz with 512 mgs ram I woke up one morning and saw that I had a stop error window open on my computer explaining that Windows had encountered some kind of driver error and that windows had to restart. Windows offered that I review my system for serious errors. That is a very sad thing to wake up to. So I ran a chkdsk and defragged and a virus check. No problems there. I checked my ...