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 39880 articles. 2 followers. Follow

1 Replies
760 Views

Similar Articles

[PageSpeed] 18

"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:

Restore Outlook 2003
I had a problem with my HD which required reformatting the drive. Fortunately, I did have my outlook.pst file backed-up. I have now reinstalled Windows XP Pro and MS Office 2003. When I entered Outlook, I did not see my email messages or contacts. So, I copied my backed-up copy of outlook.pst to the outlook directory (beneath the My Documents directory) and clicked File/Open/Outlook Data File. Then they appeared in separate places on my screen. I have several questions at this point. 1. Have I proceeded correctly so far in trying to get back to my original state before the HD problem? 2....

Install OWA 2003
HI How to install OWA2003 in one another server, separate of the server of the EXC2003? Examplo, in server WEB. Tks You can't. OWA is now integrated into Exchange and cannot be separated like you could with Exchange 5.5 installing only OWA. -- Ben Winzenz Exchange MVP MessageOne "Alysson" <alyssondk@yahoo.com.br> wrote in message news:%23yzn1wyrFHA.2348@tk2msftngp13.phx.gbl... > > > > HI > > How to install OWA2003 in one another server, separate of the server of > the > EXC2003? > Examplo, in server WEB. > > > Tks > &g...

Formula #36
Can anyone help me with a formula I use in my payroll. IF(F33<38.85,F33*10%,(F33*22%-4.66))+IF(F33>604,F33*40%-4.66) I realise the add sign is wrong because it is adding the middle section (22%) to the end section (40%) instead of an and/or choice. Any ideas? Try the following... =IF(F33<38.85,F33*10%,IF(F33<605,F33*22%-4.66,F33*40%-4.66)) Hope this helps! In article <20041021075952.29642.00002522@mb-m12.aol.com>, jonesdinah@aol.com (Jonesdinah) wrote: > Can anyone help me with a formula I use in my payroll. > > IF(F33<38.85,F33*10%,(F33*22%-4.66))...

Exchange 2003 getting clogged?
Hello all, A client of mine wrote me the following IM's earlier regarding his boss's email account. It appears that there hasn't been any change in configurations on the server and that it was just noticed today/very recently. While I'm not an Exchange2003 expert, I have used it a little and never ran across any clients that have used it before until now. Does anyone have any suggestions at what route to take when reading this? I admit this discussion with me and the client seems to be broad, but I was trying to gather as much info as possible to troubleshoot: Clie...

2000 Server Connection to 2003 over VPN
Here is my issue: My company has a remote office in Montana. We have 2 Cisco 501 Pix connecting the sites via VPN. The 2003 Servers from WA can see the Montana office with no problem as can all the Workstations. The 2 windows 2000 SP4 Servers however can not see the 2003 server in Montana or vice versa. When pinging the servers from either side it does reolve to the correct IP however the ping drops. Every other IP connects fine. Something in the 2000 servers are blocking the connection over the VPN any ideas would be great... Thanks Jeremy SOme things to check: DNS zones on both ends, st...

Conditional formatting formula for when 1 cell if blank _and_ another cell is not?
I know how to do conditions all by themselves. In this case, if J2 is blank I'm using this to set the cond form for the cells K2 and L2, respectively: =(ISBLANK($J2)) But I've never used 2 conditions about 2 different cells. If J2 is blank but B2 is not blank, _then_ is when the cond form should take place. The 2nd condition by itself would be something like this: =NOT(ISBLANK($D2)) But I don't know how to put the 2 together. Thank you! :oD Try this... =AND(ISBLANK($J2),NOT(ISBLANK($D2))) Rick "StargateFanNotAtHome" <IDon'tAcceptSpam@NoJunkMail.com>...

Access 2007 12-02-07
I need help! I want to use access 2007 and my database was build in access 2000. It have a lot of forms, macros, vba... Will I have problems or can open my database in 2007 and continue my work? Thanks Yes, you can use Access 2007 to work on a 2000 database. In fact, my defaults are set to use the Access 2000 format (currently). But, it would probably be good to make a copy just as a backup so you can get back if you do something that causes a problem. -- Bob Larson Access World Forums Super Moderator Utter Access VIP Tutorials at http://www.btabdevelopment.com _______________________...

Outlook 2003-Does Not Terminate
I was noticing that my computer would take an inordinately long time (like 10 minutes) to complete its shutdown from time to time. I've finally isolated the problem-- it only occurs when OUTLOOK.EXE is still shown as a running process in the Task Manager. (Note that the APPLICATION is not running, just the process, showing 16,788 KB.) I've tried using the END PROCESS command, but to no avail. (I get the warning message, and click YES, but nothing happens.) How can I find out what's causing this, and how do I stop it from happening? There doesn't seem to be any rhyme...

How to rebuild public folders in Exchange 2003
I have run into a problem with a migration from Exchange 2000 to Exchange 2003. The public folders appear to be messed up. This stems back to a migration from 5.5 to 2000. At this point the public folders are empty. I would like to rebuild them, but am looking for the best approach to do this. I have seen article 822444, but it also references rebuilding the public folders. Any Advice? Thanks ...

Sort Inbox items by Unread in Outlook 2003 doesn't work
I customized my inbox view to sort items by unread. Unfortunately, the messages are not sorted have read and unread messages intermixed in my inbox (sorted by date). I wanted unread messages first then read messages afterwards. The primary sort would be unread ascending (descending didn't work either) and secondary sort would be by date. The group by is none since unread was not an option and automatically group according to this arrangement is turned off (tried with it on & didn't work either). The inbox shows that it is arranged by custom. Any suggestions on how to fix this...

new excel 2003
I work in a small office and wonder if we should buy the new Excel 2003 to upgrade from Excel 2000 that we are currently using. I cannot find anywhere that tells me what the upgrades are. I also want to find some support I can get to explain the differences. thnx Hg, It's probably not worth the price of the upgrade. There's really not much more that you can do with 2003 that you can't do in 2000. One of the Excel gurus who has written some excellent books on Excel (including 2003) has a review on his web site. Take a look here: http://j-walk.com/ss/excel/xl2003.htm John ...

How to change where Outlook 2003 saves attachments
I have followed Microsoft's instructions on how to change where Outlook 2003 saves attachments. Those are found in Knowledge Base Article #823131 and involve changing the Registry. (There really should be an easier way.) Anyway, despite having followed those instructions explicitly, Outlook continues to save emailed attachments in a folder buried deep in the C: drive that doesn't even show itself when I navigate to it (and even when I set folder options to show hidden folders). Just to try everything, I did a completely clean reinstall of Outlook and then followed the Registry edit ...

Upgrade from SBS 2003 to Standard 2003
Hi Can anyone help me please? I have an sbs 2003 server and we want to change everything to the new sever we bought which is running standard 2003 r2. We only use our server as a file and print. So all we need to to be bale to transfer the users etc and have them logon with no issues ie changing profiles etc. Can this be done? Thanx PS, really dont want to get involved with transition packs etc. -- jonoharper ------------------------------------------------------------------------ jonoharper's Profile: http://forums.techarena.in/members/171848.htm View this...

Convert to Outlook 2003
We are receiving new pc's which have Windows XP and Office 2003. Users vary on operating systems and Office Versions. What are the correct step to bring over there pst files? Or maybe we shouldn't. But I need to know what the steps are to install there Old Messages/Folders/Contacts/Tasks/Calendar Items etc. from old pc(various Outlook versions 2000 prior) to new pc which now have Outlook 2003. I have tried a couple of ways but I keep getting a duplicate personal folder which is also active. (Example if they get a new mail message it goes to both inboxes in both personal fol...

outlook 2003 new mail sound
why doesn't 2003 play a sound when new mail arrives? yes i have set it up in windows sounds and it still doesn't do it You mention that you have it set up in Windows....do you have it enabled in Outlook? Click Tools, Options, Preferences, Email Options, Advanced Email Options, and ensure it is checked under new mail options. >-----Original Message----- >why doesn't 2003 play a sound when new mail arrives? >yes i have set it up in windows sounds and it still doesn't do it > > >. > anonymous@discussions.microsoft.com wrote: > You mention that yo...

Formulas in Excel using [R-1] not A10 references
I converted a Word document into Excel 2003 and when creating formulas,they are formatted as the cell the formula is in plus or minus a number of rows or columns. An example: =SUM (R[-4]C:R[-1]C). I would like to see these formulas in the regular format referencing Row and Column number i.e. =Sum A1:V52 Tools >Options, the General tab, uncheck R1C1 reference style "Drregion" wrote: > I converted a Word document into Excel 2003 and when creating formulas,they > are formatted as the cell the formula is in plus or minus a number of rows or > columns. An example...

Exchange 2003 Standard
Hi all, Our Exchange 2003 Standard Edition server's Public Store has exceeded the 16GB limit and shut down. I'm familiar with the temporary extension to 17GB one can do for the Mailbox Store but haven't found a KB article regarding temporarily expanding the Public Folder store. Is this possible and if so how? Temporary solution found - the same registry entry mentioned in KB828070 except located in the public folder key does the 1GB increase. "MS Newsgroups" <msnews@mailinator.com> wrote in message news:erikqn9qEHA.2604@TK2MSFTNGP10.phx.gbl... > Hi all, > ...

unable to paste Excel 2003 chart into Outlook 2003
I have a user who's unable to paste an Excel 2003 chart into Outlook 2003 email message. In Outlook options, the checkbox is selected for "Use Microsoft Office Word 2003 to edit e-mail messages". When I tested this on my own computer running the same version of Office, if the box is check, I have no problem pasting; if this box is cleared, I cannot paste. But on his computer, it doesn't work regardless. Thanks and regards, TL ...

Contacts in Outlook 2003 (and other Office 2003 atrocities)
Every time Microsoft does a new improved version of Office it ruins what few features it had before that worked. Going to Office 2003 (from 2000)makes a real mess of almost all of Office (it ruined what formatting I had in WORD -- the numbering goes to some default that MS thinks I *should* want). It also makes it impossible for me to find people (not CONTACTS, these are FRIENDS) I had in my address book in Outlook. For example I used to be able to type in my friends' nicknames, but that doesn't work in 2003 (the nickname is there alright, but you can't retrieve them by typing t...

Error when sending pub 2003 document as an email
we have a document that we have been sending as an email in publisher 2003 for some time, by using the SEND AS EMAIL> SEND THIS PAGE AS A MESSAGE. This has worked fine for some time, but now something has happened to the document that when you click the send button in outlook you get a message that publisher has encountered an error and needs to close. Outlook then freezes and has to be cntrl-alt-del to kill it off. this happens with the same document on multiple computers, whereas the same computers can complete the task using a newly created document. Anyone seen this before? detai...

Format Issue of Windows Mail and M.S. Group
Hi: When clicking to open any mails including this site, MS Newsgroup, top right pane of 'X' part is not visible. In the past, I used 'Shove-It' program that was found on the web-site, priced $15 that corrected this issue. However, this program is no longer effective, thus it went back to the original format, .... not fully opening content of mail of right side of pane, although I can read any mails by using 'maximizing bvutton' to open in full. Any imputs on this, above would be greatly appreciated. Thanks, "Winter10" <Winter10No...

Outlook 2003 #240
Just installed Outlook 2003, running Outlook 2000 prior. Here is my problem; 1) I launch Outlook 2) Microsoft LDAP window comes up; Server name NULL, PORT 3268, USER NAME NULL 3) Close this window. Then I can press send/receive button, however, it cannot check for new email. In addition, if I create a new email from within Outlook, there is no "send-emal" button. HELP! Thanks. Steve <nospam@abc.net> wrote: > 1) I launch Outlook > 2) Microsoft LDAP window comes up; Server name NULL, PORT 3268, USER > NAME NULL > 3) Close this window. Tools>E-mail Accoun...

Sync issue
I have a Sansui player, and it's already been sync'd a couple of times. Sometimes it doesn't appear to be connected on WMP. It just says "connect device now" I haven't been able to sync for a while now. ...

formula probles
i posted this in general forum but i think its better suited here - got a worksheet such as this: SequenceId Musician Initial YOB YOD Instrument 1 Micheal A. 1975 1999 Chello 2 Tom I. 1960 1989 Piano 3 Hans J. 1982 2000 Piano 4 Hans J. 1982 2000 Keyboard 5 Hans J. 1982 2000 Orchestra I want to be able to retrieve every information sequenceid,initia yob,yod just by matching the name - i went through various match if vlookup etc formulas but nothings working - at best i got the las matches but not all of the possibilities - for example i only get 5 j hans 1982 2000 orchestra but not all of h...

Pub 2003 Question ( Problem )
I have documents I created a couple of years ago in Pub 2003 that contain a lot of pictures. When I open them up today, many of the pictures do not show on screen? No error messages ? What can I do ? Wayne wrote: > I have documents I created a couple of years ago in Pub 2003 that > contain a lot of pictures. When I open them up today, many of the > pictures do not show on screen? > > No error messages ? > > What can I do ? =========================== Have a look at the following site: (Read the third FAQ) Q: Why can I not see images/shapes/ lines when editing ...