Tracker
HOME    TRACKER    Excel

# AVERAGEIF Function 2003?

## I need something like AverageIf function that basically takes the B column and averages only the numbers that are not 0… I have messed around with this and finally just got frustrated, maybe its because I’m using Excel 2003?

Related Forum Messages:
AVERAGEIF Function In 2003
I have data that i need to average. In Excel 2007 there seems to be a function to do this but it isnt available in 2003 version.

In column A there is a load number in column B there is a total load time, i need to be able to calculate an average load time.

AverageIf Function To Average A Range Of Numbers
I'm trying to use the average if function to average a range of numbers, only if a different range of numbers numbers equal a number in two different cells.

ex. average A1:F1 if G1:L1 equals M1 or M2

Sum Function Visible Cells Only In Excel 2003
I have a worksheet of data that has 12 columns of numbers (sourced from an external data source) that I have a subtotal for each row. I need the user to be able to hide columns that they do not want included in the subtotal. I have found references to Subtotal(109,D2:Q2) on the net that apparently does this but it doesn't make any difference whether or not I hide columns (I am not using filtering).

Does anyone know how to do this using a formula, (I would prefer to keep this a macro free worksheet)

Averageif, ###############, H:mm
I am trying to calculate time in the ER, avg. time in the ER, and time before seen in minutes USING the Averageif function. However, in the columns that I am trying to calculate, there are a lot of ######### and 0:00. So I thought that if I use =AVERAGEIF(AL2:AL47,">0") then that would exclude the ######## and the 0:00.

However, it seems that the formulas are not working. For example, one column contains 00:10, 00:04, 00:05, then a lot of ########, and 0:00.

When I take the =AVERAGEIF(AL2:AL47,">0") of this whole colum then it gives me and average of 18:02!!!!

18 hours and 2 minutes can not be correct!

AVERAGEIF Returning #DIV/0!
I am using the following formula, but for the cells that dont have data I get the #DIV/0! error.

=AVERAGEIF(\$B\$6:\$B\$48,B59,\$W\$6:\$W\$48)

Retain 2003 Code In 2007 And Back To 2003
I've created a workbook with code to email it to a reviewer then back to the originator when reviewed (along with other editing functions etc.). The problem - The originator is working in Excel 2003, the reviewer in 2007. The macros work great until the reviewer sends it back. I have not been able to figure out which FileFormat:= ???? to use when I SaveAs prior to emailing as an attachment. My Excel 2003 doesn't recognize the .xlsm file and other formats are eliminated the vba/macro coding

Averageif A Number Range
I cant figure out an averageif formula for a particular criteria. my criteria is any number between 10000 and 20000. Here is an example.

=AVERAGEIF(C:C,"between 10000 and 20000",G:G)

1120512112035

Trying To Imitate AVERAGEIF() With An OR() Test.
I've got a spreadsheet I use to track quality scores, it will appear like such, and with 2003 I don't have AVERAGEIF()

name | team | score
joe f | billing | 3
may r | billing | 3
roy m | status | 1
kay t | first call | 1
etc...

I already used an array function to find the average of each team (even with empty scores) by using:

=AVERAGE(IF(DeptRange=\$B\$7,C\$20:C\$200)) [Ctrl-Shift-Enter]

EDIT: Incidentally, I changed AVERAGE() to COUNT(), and it kicked back 9 values instead of 3 (I have one value entered for each team atm). B7 is the name of the department I'm finding the average for. I've got 3 departments in one location, and 6 in another location. I'm trying to add OR() to give me our regional quality averages, and it's not working. I used:

=AVERAGE(IF(OR(DeptRange=\$B\$7,DeptRange=\$B\$9,DeptRange=\$B11),C\$20:C\$200)) [Ctrl-Shift-Enter]

AverageIf Multiple Conditions
I currently have this equation: =AVERAGEIF(\$B\$2:\$B\$900000,">="&L5,\$C\$2:\$C\$900000). Where L5 = 100. I'm interested in having the formula averageif B column value is between L5 & L6. Where L6 = 200. So I want it to find all values in B\$ that are between >= 100 but < 200, and then average their C\$ counterparts.

SUMIF/AVERAGEIF With Multiple Ranges
This is what i was using,

=AVERAGEIF(\$B\$198:\$B\$240,B254,\$C\$198:\$C\$240)

I want to add more sets of ranges and average ranges like this,

=AVERAGEIF((\$B\$6:\$B\$48,\$B\$70:\$B\$112,\$B\$134:\$B\$176,\$B\$198:\$B\$240),B262,(\$C\$6:\$C\$48,\$C\$70:\$C\$112,\$C\$13 4:\$C\$176,\$C\$198:\$C\$240))

Which of course returns #VALUE!

AverageIf - Finding Similar Text
I've searched around the web for AverageIf solutions and found the CSE formulas. However I've come across a big problem I can't resolve.

I have two columns of data. Column A has _similar_ text and column B has data.

The problem is the following: AverageIf does not support finding similar text. This means that a formula like:
=AVERAGE(IF(A2:A400="Info/German*";F2:F200;FALSE)) does not work.

Column A contains words like Info/German, Info/German2, Info/Italian etc. But I only need to find the average of cells that begin with "Info/German".

Opening 2003 Files From Word 2003
how to write a code where i can open Excel2003 file from Word2003 and delete data from Sheet1 and then close the excel2003.

All this is done from Word file.

Selecting MS Word 2003 Table From 2003
I can select a MS Word 2003 Table from my Excel 2003 macro? I keep getting Error 438 as per attached file for the line of

Average If In 2003
I'm trying to do an average if statement, but I only have excel 2003 and don't think the averageif statement is available.

So... Here is what I have.

In column A I have have a list of H's and @'s. In column b I have a list of numbers that I want to average out.

Basically I want to find the Average for all the #'s in column B that have an H in column A.

#NAME (2000 OK 2003 NO)
Excel 2000 spreadsheet everything is fine.

When I open the same file in excel 2003 #NAME? Error appears.

I check Accept Labels in Formulas and it helped in some places but not everywhere.

I still have lots of NAME errors in a relatively simple spreadsheet.

I Excel 2000 everything works fine.

Text In 2003
When I enter the letter s in cell A6 it auto.shows Sept.also the letter d shows Dec.automatically etc.. how to stop this...

IF THEN String For 2003
I'm looking for a formula to say if the contents of cell A1 are this and the contents of cell L1 are this, then enter value 1 in cell N19.
For example if A1=Text and B1=Diff Text then A14=1, but if A1 doesn't =Text or B1 doesn't = Diff Text then A14=0.

2003 Is Too Far Up On The Screen
I did the restart our company required and when Excel reopened it was on my laptop rather than the big screen. I run 2 screens. Excel 2003 is now so far up on the laptop screen, I cannot move it. I can barely see File, Edit, View, etc. How can I drag Excel down so I can resize it?

Month Plus One - 2003
I would like to find a formula that will take the current month (i.e.
May) and add one month and return (display) just the following month (i.e.
June)

Getting #Value Errors In 2003
I can not get the following formula to work - I keep getting #Value errors and I've checked the fields and the values are correct

=SUM(IF(Input!\$A\$6:\$A\$4006=1,IF(Input!\$I\$6:\$I\$4006="DM",IF(Input!\$K\$6:\$K\$4006="Bid",Input!\$L\$6:\$L\$40 06,0),0),0))

My intension is that if A=1 and I=DM and K=bid then add the corresponding values in L and display. I can't figure out why this formula in another cell and works fine

=SUM(IF(Input!\$A\$6:\$A\$4006>0,IF(Input!\$A\$6:\$A\$4006<1,IF(Input!\$I\$6:\$I\$4006="DM",IF(Input!\$K\$6:\$K\$400 6="Bid",Input!\$L\$6:\$L\$4006,0),0),0),0))

it is just checking an additional condition of the value in column A

Filtering In 2003..?
I have a filter that works in Excel 2007, and I'm trying to figureout how to make it work in Excel 2003. OrderNums is an array of multiple criteria. The below code works perfectly in Excel 2007.

2003 Sorting
I have the folowing simple sorting exercise

Col A Col B
1 56(=A20) 224(=A1*4)
2 66(=A21) 396(=A2*6)
3 33(=A22) 198(=A3*6)
4 13(=A23) 26(=A4*2)
.
.
.
20 56
21 66
22 33
23 13

I am trying to sort on Col B in descending numeric order, and cannot seem to get this done with the 'data>>sort ...' function/option.

Using DATE In 2003
I need to create a date using the DATE() function, and have been experimenting with:
=DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW())) to try and generate today's date as an exercise in using the DATE function without success.

4.0 Macro In 2003
I have a workbook that on opening I see the following message. This workbook contains one or more Microsoft Excel 4.0 macros. What's confusing is that I never downloaded any 4.0 macros. The ones that I have were either recorded or provided by this forum. I am trying to find this macro and delete it but I can't locate any 4.0 macros. I even copied all worksheets to a new workbook and when I go under Tools/Macro there are 0 displayed. I even launched the VB Editor and can't find any.

It's not causing any problems even when I select Yes to enable these macros but it may cause some individuals to not want to open it which is a problem. I've also run Norton Antivirus and none were found. Additionally, if I open other workbooks this message is not displayed. Could someone have a look at the attached and see if they can identify what macro the message is referring to?

Legacy Vba App Won't Run In 2003
My old Excel app which uses the Kodak ImgEdit control runs fine in Off97,2000, & XP/2002, but the VBA code breaks in XL2003. Even some normal VBA code, like direct sheet references such as

Worksheets("Data").Activate

kills execution, whereas

Dim sDat As worksheet
Set sDat = Worksheets("Data")
sDat.Activate

seems to work - a behaviour rather like a bad/missing VBA reference.

Only the ImgEdit ocx is used, and seems to register correctly (needs imgcmn & oieng400 dlls) using Regsvr32. The ocx is on a worksheet, not a form. And a side-effect is the Excel is using 90% cpu time while this workbook is loaded, although all vba is stopped/reset.

The only clue I've found is that starting a new project and trying to drop ImgEdit onto a 2003 sheet returns a "Cannot insert object" error, while trying to drop it onto a form returns a "The subject is not trusted for the specified action" error.

Or there a document detailing the differences between 2002 & 2003?

Vba Sql Syntax 2003
In VBA Excel 2000 while ODBC connecting to a dbase file:

parameter=" N0011"
...
ActiveSheet.QueryTables.CommandText = Array( _ ...
"SELECT ... & Chr(13) & "" & Chr(10) & _
"FROM ... & Chr(13) & "" & Chr(10) & _
"WHERE (dbase_file.field1=" & chr(34) + parameter + chr(34) & "... & Chr(13) & "" & Chr(10) & _
"ORDER BY ...")
...

It doesn't work any more in Excel 2003. Of course it works directly such as:
"WHERE (dbase_file.field1=' N0011')
but I couldn't find the way to replace the ' N0011' with the parameter.

It is not the singular issue...
"FROM dbase.file dbase.file " that worked well in Excel 2000, the Excel 2003 "wants":
"FROM 'drivepath'dbase_file dbase_file "
and I couldn' find a way to replace the explicit 'drivepath' with a predefined variable.

Crashing Between 2003 & 2007
I've been putting together an automated spreadsheet that has external data sources to SQL. I have been having some strange problems with it between versions of Excel. Unfortunately, the company's standard is 2003 but quite a lot of the company use 2007. I have been developing this report on 2007, the server that runs the automated version of the spreadsheet runs 2003 (and cannot be upgraded beyond 2003 as it is running Server 2000!).

The template opens fine on my 2007 machine and all development work has been run on my machine including the downloading of data. The VBA script runs something like

refresh datasources ........

Copy And Paste VBA (2003)
I've written some very simple code to copy from one sheet and paste into another:

Sheets("t").Select
Columns("A:E").Select
Selection.Copy
Sheets("Code by QC").Select
Range("A1").Select
*ActiveSheet.Paste

When i run it, it works but I get a

*run-time error '1004':

Paste method of Worksheet Class Failed

Why is it doing this even though it's pasting what i want it to paste? It's preventing the rest of the macro from running.

Could it be due to me copying data from a filtered set

Convert From 2007 To 2003
I created a Macro in 2007 and it turns out I needed to develop it in 2003. Now none of my code works and I am trying to convert it. I would appreciate any assistance determining what needs to be corrected and how to correct it. Here iare two samples that is repeated throughout my code.

Application.CutCopyMode = False
Range("A2:N1048576").ClearContents
Range("A2").Select
ActiveWorkbook.Save

Windows(BBSource).Activate
lastRow = Range("C14").End(xlDown).Row
Range("C14:C" & lastRow).Copy
Windows(BBForm).Activate
Range("J2").PasteSpecial Paste:=xlPasteValues
Range("K2").Value = "PCS"
Range("K2:K" & lastRowDest).FillDown

Conversion From 2003 To 2007
My company used to have Excel 2003. I had a PivotTable in a worksheet that accessed data in another, completely different worksheet. When we converted to Excel 2007, I saved many of my Excel files from .xls to .xlsm. My PivotTable that once accessed data from another .xls file is still trying to access that same .xls file. What I want, though, is for it to access the new .xlsm file.

Replacement Of COUNTIFS Within 2003
Assist me in getting the following statement into Excel 2003 format - it is working OK in Excel 2007.

=COUNTIFS('Sheet 1'!\$AB:\$AB,"True",'Sheet 1'!\$F:\$F,'Sheet 2'!B13,'Sheet 2'!\$J:\$J,">="&H2,'Sheet 1'!\$J:\$J,"

Double Value Vlookup In 2003
I have a spreadsheet of mail types with totals. What I am trying to do is create a formula in the highlighted cells in the second sheet that will look at the Account ID Row for a certain value (in this case '45265637'), then look for all the month (in this case April) then add all the figures for this month in the LETTERS row, with the cell below it doing the one for PACKAGES.

I am exhausted from trying to get this one done. The reason that I need to this is that is I have an Excel system readout of 821 different ACCOUNT IDs and their volumes for everyday from 1st April 08 - 30th June 08 and I need a forumula that will extract this automatically as I don't fancy manually cell referencing 45,000-50,000 cells!

(and I hope the images work!)

http://www.mediafire.com/imgbnc.php/...5942a7002g.jpg

http://www.mediafire.com/imgbnc.php/...a3b756c52g.jpg

Conditional Formating In 2003

http://www.mrexcel.com/forum/showthr...itional+format

Asks almost exactly the question I have, but I can't make heads or tails out of the answers, I'm guessing becasue I have Excel 2003 and not 2007 as mentioned in the replies.

In column "A" I have a number; column "B" the formula =A1 copied on down and in column "C" either a 1 or 2 or a 3.

If the number in column "C" is a 1 then the font in the same cell in Column "B" should be black, 2 Blue and 3 Red.

I'm thinking this should be very simple, but so far it's not turning out that way. )-:

Matching Cells (2003)
I have a list of unique numbers on sheet 'List'. On another sheet, 'Data', I have a columns of data and column E contains a number which will match a number in the list on sheet 'List'.

What I want to do but don't know how to is loop (i think) through the numbers in 'List' and every row in 'Data' that contains a number is moved so that the data is grouped according to the number. i.e all rows with 78878 will appear beside each other and so on.

Neec code that i could use that will automate this so the user can just press a button and the data is reorganized?

How Many Colours Are There In 2003 And 2007?
For a particular requirement I want to utilise the colours.
ANd therefore I need to know, how many colours are present in the Colour Palette in both 2003 and 2007 and which colours would be same if I make the File in 2007?

Are there 60 colours in 2003?

2003 Macros In 2007
I have a spreadsheet that collates a list of data from week order and puts them in a graph. I have a macro created that basically does the graph, it works fine in 2003 but not in office 2007. I get an error "Run Time Error '1004' Application-defined or object-defined error, then when I click the debug button it highlights "ActiveSheet.ChartObjects.Delete". I'm not sure if when its trying to create a new graph its attempting to delete the old one and isnt deleting it? The full code for the macro is below:

Dim searchResult As Range 'Result of the find operation - to check if it has worked or not
Application.ScreenUpdating = False
If SheetExists("OTIF Chart") Then
Sheets("OTIF Chart").Delete
End If
Sheets("OTIF's").Select
Range("B4:N4").Select
Selection.Copy
Sheets("Graph").Select
Range("B1").Select................................

Conditional Formatting In 2003
I use Excel 2003. I want to do a conditional formatting on a cell (e.g. A1), which value is from an If() formulas. The value could be a percentage or a text “DataNAvail”. The conditional formatting on A1 is based on another cell’s value (e.g. B1), which is also a percentage.

Since in Excel 2003 there are only three options for conditional formatting. I used two for green (A1 greater than B1) and yellow (formulas in A1 equals AND(A1<B1, A1>=B1*0.75)). I want to set A1 to red when its value <B1*0.75 or is “DataNAvail”. Since they are not the same type, I don’t know how to do it.

Average Of The Whole Column In 2003
I'm using Excel 2003, so no AverageIf.

I have 15,000 rows of data.

In Worksheet_1, I have:

Column A (Date) contains daily dates ranging from January 1968 to today, though not every day is listed (weekends and holidays are excluded).

Column E (Price) contains daily prices.

On a separate worksheet (Worksheet_2), I need to calculate average monthly prices, listed by end of month dates (Monthly_Date and Monthly_Price, respectively).

I've been trying to use an array formula like this:

=AVERAGE(IF(AND(MONTH('Worksheet_1'!A2)=MONTH('Worksheet_2'!A2),YEAR('Worksheet_1'!A2)=YEAR('Worksheet_2'!A2)),'Workshee t_1'!E:E,"0"))

I can get an average of the whole column, but I cannot seem to make Excel "grab" only the corresponding data in Column E that matches the right date criteria (in this case, when the month=January and the year=1968).

Relational Macro In 2003
I see in Excel 2007...there is an option of using "relative references" in a macro.

Is there any way to do that in 2003?

Typing Accents In XL 2003
Is there anyway to type accents in Excel the same way you can do in Word,
for exemple ctrl+' before e gives é

I am using the Language setting "US and Western Europe" for Office Language settings.

2003 Color Palette Changes
when I am in some of my spreadsheets, the background colors for the cells have changed. In fact the entire color selections are slightly different from what is normal. Then if I close out all sheets I have opened and reopen them in excel they are back to normal.

Sending Emails From 2003
I have a macro which uses Outlook to send an email. I know it's not possible to over-ride the annoying 'Another program is trying to send an email....' dialogue box but I wonder if there is a way of exiting sub if the user clicks No on that dialogue box?

2003 Time Format
I have it set to format of mm:ss but whenever I enter a time of greater than 23:59 it subtractes 23:59 from the time autopmatically. any ideas how to stop this from happening?

i have note pad file. for your kind information i attched the note pad file i like to convert this note pad file to excel. i want each coloums and rows separate separate same like note pad in xls.

Cannot View Sheets In 2003
I am using some protected workbooks and cannot see the tabs at the bottom of the screen.

I have tried going to tools/options/etc

I have tried viewing full screen

If I copy the current sheet 1 and paste it into a new workbook I can then see tabs (but only sheet 1 has copied data).

Compatibility Between 2003 & 2007
I would like to send a .xls file created in Excel 2007 to a user who will work with this file on a PC that runs Excel 2003. Will they be able to open and use a file created in a newer version of Excel?

Auto-format - 2003
I am pasting output from SAS into a spreadsheet. The data I am pasting cannot be put into columns beforehand. When I do text-to-columns, the data are auto-formatted. This is how it looks before text-to-columns:

159 xx Char 2 2. \$2. xx

This is how it is auto-formatted:

159xxChar22\$2.00 xx

The loss of the period [2 to 2.] and the addition of zeros [\$2. to \$2.00] is a disaster because it makes my SAS code non-functional. Is there anyway to stop auto-formatting in text-to-columns? Is there a more user-friendly software besides Excel that would be better to use?

(2003) Get Day Of The Week From A Date
I have a list of dates in Excel that I would like to pull the day of the week it was on: for example, cell A1 has '1/03/09' and I would like it to display in B1 what day of the week that date was.

I downloaded the "Microsoft adCenter Add-InBeta for Excel 2003" from: [url] and installed it at my pc, but after installation was complete, when opening excel 2003, I can´t see the addin anywhere. I am supposed to see a tab at my toolbar.

Autoupdate Table - 2003
I am currently working on a workbook designed for resource planning. It contains a worksheet named "The Team" which contains information about every person on the team. It has columns for First name, Last name, and Division.

There is also a worksheet for each division; Each contain 3 sections that are either inputs or formulas, with the people of that division in column A.

My issue is adding people into this workbook. At the moment, if I want to add people into this workbook, I have to insert a row in "The Team", fill out their info, and then go to their division's sheet and insert a row in each 3 sections. This is a rather tedious way to do it and I wanted to know if there was some way I could automate it.

At the moment, I have created a macro that serves to Insert a row and copy formulas, but leave constants blank. It is nice for inserting the rows, but I still have to manually go to the division sheet and execute the macro in all 3 sections.