I Need an answer for this Formula

I am using excell 2007 & this formula works
{=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)}
When i upload this workbook to a 2003 version this formula does not work I get
{=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this
Then in the cell with this formula has a NAME error WHY & HOW could i fix 
0
Utf
12/23/2009 5:15:01 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
834 Views

Similar Articles

[PageSpeed] 17

The IFERROR function can only be used in Excel 2007.

Try this array formula** :

=LOOKUP(1E100,CHOOSE({1,2},0,AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5)))))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"Mike" <Mike@discussions.microsoft.com> wrote in message 
news:2BB6D6FA-701F-487E-BB2E-100A74BD6B80@microsoft.com...
>I am using excell 2007 & this formula works
> {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)}
> When i upload this workbook to a 2003 version this formula does not work I 
> get
> {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} 
> or somthing close to this
> Then in the cell with this formula has a NAME error WHY & HOW could i fix 


0
T
12/23/2009 5:46:19 AM
IFERROR was introduced in Excel 2007... it is not available in Excel 2003.

You need to use ISERROR with IF to get similar functionality
Instead of 
IFERROR( Your formula, value if error ) 
use
IF( ISERROR(Your formula), value if error, Your formula) 

"Mike" wrote:

> I am using excell 2007 & this formula works
> {=IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)}
> When i upload this workbook to a 2003 version this formula does not work I get
> {=_xinfl.IFERROR(AVERAGE(IF(MOD(COLUMN(G5:HC5)-COLUMN(G5),4)=0,IF(G5:HC5>0,G5:HC5))),0)} or somthing close to this
> Then in the cell with this formula has a NAME error WHY & HOW could i fix 
0
Utf
12/23/2009 5:47:01 AM
Reply:

Similar Artilces:

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes =sheet1!$B$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I have tried entering =!$B$2*3 which works initially but recalculates using the $B$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Most unusual question
Using Outlook Office 2003 and this evening, everytime I try to open a link from email in Outlook, it opens up My Documents! What's wrong and how do I correct this problem! Thanks! ...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Need VB5 code for these VB6 instructions
I am trying to use the vbSendMail.dll (written in VB6) in a VB5 project. Need Vb5 code for: Private WithEvents poSendmail as vbSendMail.clsSendmail Private Sub Form_Load() Sdet poSendMail = New clsSendMail End Sub Help!! On Tue, 26 Jan 2010 13:33:01 -0800, Dennis Rose <DennisRose@discussions.microsoft.com> wrote: >I am trying to use the vbSendMail.dll (written in VB6) in a VB5 project. >Need Vb5 code for: > >Private WithEvents poSendmail as vbSendMail.clsSendmail > >Private Sub Form_Load() > Sdet poSendMail = New clsSendMail >...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

Automatically fill down formulas when adding a record
I have a data entry sheet which receives one record per row. Some of th columns contain calculated fields and will be hidden. Is there a way t have the formulas automatically fill down when new records are added Thank you -- Message posted from http://www.ExcelForum.com One way is to use Excels built-in dataform. data>form -- John johnf 202 at hotmail dot com "steveski >" <<steveski.11dx8c@excelforum-nospam.com> wrote in message news:steveski.11dx8c@excelforum-nospam.com... | I have a data entry sheet which receives one record per row. Some of the | columns con...

date formulas
Hi, I have two problems that need help. Cell A1 has a date of birth. Cell A2 calculates the age. If there isn't a dob of birth, cell A2 returns 110. Another one is having cell C1 with a date or NA. C2 is based on cell C1 and adds 3 months to the date in C1 but if C1 has NA, the return is #value!. Kathleen Describing the problem is only the first step, Kathleen. You also need to identify the solution you are looking for. As a guess, for #1 try, =if(a1="","",datedif(a1,today(),"y")) For #2, =if(c1="NA","",date(y...

sum if formula
=SUM(IF('Day 1'!$C$4:$C$37=A22,'Day 1'!$D$4:$M$37))+SUM(IF('Day 2'!$C$4:$C$37=A22,'Day 2'!$D$4:$M$37))+SUM(IF('Day 3'!$C$4:$C$37=A22,'Day 3'!$D$4:$M$37))+SUM(IF('Day 4'!$C$4:$C$37=A22,'Day 4'!$D$4:$M$37))+SUM(IF('Day 5'!$C$4:$C$37=A22,'Day 5'!$D$4:$M$37))+SUM(IF('Day 6'!$C$4:$C$37=A22,'Day 6'!$D$4:$M$37))+SUM(IF('Day 7'!$C$4:$C$37=A22,'Day 7'!$D$4:$M$37))+SUM(IF('Day 8'!$C$4:$C$37=A22,'Day 8'!$D$4:$M$37))+SUM(IF('Day 9'!$C$4:$C$37=A22,'Day ...

Formula Bar missing
I need help reappearing my formula bar. I must have pressed something that made it disappear. If I go to the “View”menu, Formula Bar has a check next to it, as if it should be showing, but I can’t see it. I tried checking and un-checking, but no where to be seen. I have two other tool bars and my status bar, that are working fine. Could anyone give me a hand with this? Thanks, I am currently using Office v. X. You may have switched "Full screen" on. To uncheck, see the View menu -- Kind Regards, Niek Otten Microsoft MVP - Excel "JorgeH" <JorgeH@d...

Need Help #5
I had installed outlook on Exchange 2003. Now when I try to republish Default global address list, I get the error "MAPI or an unspecified service provider". I think my MAPI profile must be currupted. Can anyone tell me how to fix this problem? Thank Kumar Check the version of mapi32.dll in your system32 directory. You may simply need to replace it with the mapi32.dll version that is found in the /exchsrvr/bin folder. Oh, and uninstall Outlook please. There is a reason that installing Outlook on an Exchange server isn't recommended and isn't supported.... -- Ben W...

Add to Formula
I have created a drop down list and added a formula to show me the cost of an item eg; pink slippers when that is picked from the list the price appears in the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3 (2)'!A3:C152,2,FALSE)) I've been trying to add another formula to this for inventory. So actually what my question is. How do I create a list along with a formula so when I pick the item out it will put in the price, and minus 1 or however many sold, to give me a count of what I have left in stock, and say I started with 25 of...

Need to Hire Outlook 2003 Help
Hi I'd like to hire someone that can walk me through some specific stuff with outlook 2003 I'm using the Lotus Notes to Outlook plugin My compnay is a Notes shop - which means I get NO support for outlook --- this plugin is a god send and i want to make sure I get it set up correctly It's coming out of my pocket but I'm willing to pay if I can find the right person to help me Please post ideas here or email me Thanks Brendan ...

= Text formula problem
Hi Everyone, I am using an excel spreadsheet as a linked table in an access database for an update query. I had it working very nicely for a period of time. I am not sure if some Microsoft update caused a problem or what. The field I use t o update my database didn't work until I used this formula to make a new row. I have a column of numbers which was retrieved from a data miner program it's called Acct. For some reason Access doesn't like the formatting so I insert a blank column to the right and call that PtNum. In the first cell of this column which is C2, I type ...

Why Does This Formula Return an Error??
:confused: Hi, what am I doing wrong with the following formula: =countif(sheet1:sheet31!E6:E35,">=1") FOR some reason this formula fails when I use the (sheet1:sheet31) wit the column range (E6:E35). Is it possible to include a multiple sheet count and column range i the same Formula? Thanks for any helpful responses.. By the way, the formula works ok just as long as I'm not trying to rea multiple sheets.. Please help on this -- Mh ----------------------------------------------------------------------- Mhz's Profile: http://www.excelforum.com/member.php?action=...

Need to automaticallu input date in form
This seems to be a very popular question posed on the forum, and I have read through and tried the most viable solutions given to others. I have three diferent forms that update a table with three different date fields. Although I have set the default value for all the date fields in the table to =Date()- 1, this only works on one of the forms, even though that field in each form is given same properties. The forms are used to update an inventory table containing part numbers, wip dates and amounts, packing dates and amounts, and shipping dates and amounts. I use the three different forms bec...

Need help in data copying. #3
Thanks Mr. Dave. It was really helpful Regards Tom -- SMIL ----------------------------------------------------------------------- SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=488 View this thread: http://www.excelforum.com/showthread.php?threadid=25956 ...

Is the IF statement what I need?
I have two cells that each have drop down lists. The first cell has the text yes and no, If i choose yes i want the second cell to return N/A for example, and if it is no I want the drop down list to be usable in the second cell. Thanks for any help Mezani< Yes, in a word. This is what I came up with: In cell A1, Data Validation: Allow: List Source: Yes, No In cell B1, Data Validation: Allow: List Source: 1,2,3,4 In cell B1, formula: "=IF(A1="Yes", &qu...

Formula not deleted when pressing delete???
Hi, Is it possible to protect Formula's in a cell from being deleted whe user pressing delete? Only the cells who containt formula's may not be cleared but th others may! Thanks in @dvance for trying to help me. GreetZ from Stif -- Message posted from http://www.ExcelForum.com Stift, Select the cells that are allowed to be changed. Format - Cells - Protection - Unlock. Now protect the sheet: Tools - Protection - Protect Sheet. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Stift >" <<Stift.18ct...

Drag Formula cell to decrement the cells #2
Ken, I do thank you for your prompt assistance. The solution you gave me works fine but with limited No. of cell (sorting problem) is there a way to do it in an unlimited No. of cells (e.g. 50 or 6 cells - BI till DP to be the mirror of A till BH ) Thanks Agai -- Asher Zu ----------------------------------------------------------------------- Asher Zur's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1610 View this thread: http://www.excelforum.com/showthread.php?threadid=27554 Sure, just follow instructions as before and in BI1 put =A1 and copy across to DP...

Collection STAT button needs additional info
From a Credit Managers point of view, I would like to see what all the Unposted orders, checks, invoices, etc are when I am making calls or approving orders. This info is available thru CARDS >> Sales >> Summary and pull the customer account up. We cannot see this info from within the MAIN window now - only if we click on the unposted sales button but we are not given totals in SUMMARY, we have to calculate them manually or launch this other window ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To v...

Protect: Formulas?
I'm protecting a sheet via VBA. There seem tb quite a few parms to control what gets allowed/disallowed, but I can't find anything that applies to formulas. I want the user to be able to put formulas behind some cells. Right now, with the sheet protected, the user can type data into cells, but the Sigma icon that allows formulas tb put behind them is disabled. Unprotecting the sheet enables the icon... but I want various things to remain protected. Is there a way to allow formulas, but still have Protected=True? -- PeteCresswell Pete, You have to have unlocked the cells the use...

help needed! can't open my pst file
hi.. need your expetise... :) im really desperate to open my pst files. im having a hard time to open it. i encountered errors when opening the file. like: this file is not a personal folders file. i already tried to run scanpst.exe, but no good. tried to import/export files but really cant get through. even tried the data file management option.. but still no luck! :( need help... please.. i can send you the file to try it on.. :) please advise.. thanks in advance, bambz Sorry, but the most basic information is missing - version of Outlook and where the .pst file is stored. --� Milly ...

Need Help Nesting a Formula inside MAX formula
I have Stock Market quotes that gets stored in Excel intra-day When every 30 minutes passes, the data is automatically updated and archived in the next row up and the new update begins. EXAMPLE: C2 9:30 D2 25 C3 10:00 D3 25.5 C4 10:30 D4 25.10 C5 11:00 D5 26 After 30 mins the data looks like this: C2 10:00 D2 25.5 C3 10:30 D3 25.10 C4 11:00 D4 26 C5 11:30 D5 26.25 This process keeps going on. I can archive as many periods I want, in any timeframe. I currently store 100 rows of 30 minute periods. Everytime a new peiod begins al...

Help needed- customize Outlook
I applied the 'fix' to Outlook 2000, as discussed below. Now, oddly, when away from the computer for a while, I have the start-up screen showing, asking for my password.. then I'm back to where I was, with the original programs still running as they were... I would liek the screen to go blank, as before, when away for 20 minutes.. Since I did 3 things today, could it be this fix, or the 'Net' update to XP, or the 'Messenger' update? Gotta be one of the three... the 2 XP ones mentioned are recent, perhaps today, to the Windows update site.. help? Thank you! http:...

Strange Problem with Formulas
Dear users, I was confronted with a very strange problem. Can anybody help? Basically, I have 3 worksheets: No. 1 contains all data No. 2 contains all filtered data (macro special filter from No. 1) No. 3 is the output, graphically optimized sheet, which has fixed formulas to No. 2 (e.g. =Event1!A1) Imagine, I had only these 3 sheets. Actually, I have many of them, but there are all paired as No. 1 and 2. No. 3 is the main sheets, which links too all No. 2's. To update the file, I have created a macro, which compares one excel file with the actual one. All worksheets named similarily ...