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?
View Complete Thread with Replies
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.
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!
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)
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
I already used an array function to find the average of each team (even with empty scores) by using:
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:
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,
I want to add more sets of ranges and average ranges like this,
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".
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.
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?
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
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
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.
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)
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
kills execution, whereas
Dim sDat As worksheet
Set sDat = Worksheets("Data")
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:
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("Code by QC").Select
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.
'Delete anything currently in spreadsheet
Application.CutCopyMode = False
lastRow = Range("C14").End(xlDown).Row
Range("C14:C" & lastRow).Copy
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.
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!)
Conditional Formating In 2003
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?
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
Application.DisplayAlerts = False
Application.DisplayAlerts = True
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:
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).
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?
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).
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:
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?
AdCenter Add-InBeta For 2003
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.