Calculate average of a range, but exclude errors

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - How to 'format' cells to be 'absolute' references ? How to 'format' cells to be 'absolute' references ? Trying to setup cells with date formulas (for project management) with formula cell to show date from sum of reference date cell plus number of days cell, and make formula cell an absolute value so can sort cells by that cell column (per discussion at ... http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=absolute+reference&dg=microsoft.public.excel&cat=en_US_e064c9ec-14bf-4ef7-ba07-6a1970559f64&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us ...ie, cell-3 = $cell-1(date) + $cell-2(number of days) How to get reference cells to be absolute values by formating, or set with shortcut key, or set with main menu pick ? -- akm Thanks again for your help. ---- Ah, the miracle of computers!... someone also said "To error is human, but to really screw up it takes a computer!" Tag: Calculate average of a range, but exclude errors Tag: 142003
  - 2
    - formula for total days, excluding holidays but including weekends I need a formula to calculate total days (working days) that excludes holidays, but includes weekends. The networkdays formula doesn't work since we work on weekends and have variable days off. I wanted to put the select days off( some weekdays and some weekends in a holiday range), and have them subtracted from the start date, end date range if they fall in the range. Tag: Calculate average of a range, but exclude errors Tag: 142000
  - 3
    - Excel 2007 compatibility mode + Vista offline files = broken? Problem: (the short-short-short version): Environment: Win2k3 SP2 file-server, Vista SP1 clients, AD domain with roaming profiles. Office 2007 SP1 is installed. Offline files is enabled on a share by group policy. Steps to reproduce: A pre-existing excel 2003 file is present on a share that is available offline. A user WITH A ROAMING PROFILE is logged into a vista client (yes, the roaming profile seems to make a difference) 1) Right click on the file, view properties, hit the offline files tab. The file is online. 2) Now open the file in excel 2007. Observe that Excel opens the file in compatibility mode. Right click on the file again, check status, and the file is OFFLINE. 3) Make a change and close the file (saving the change.) Check file status. Still offline. Wait a half-hour. Check again. STILL OFFLINE (not even background syncing!) There are two problems as I seem them: 1) Another user can open an already open file WITHOUT A WARNING that the file is in use. Excel 2003 used to warn that the file could be opened read-only. Compatibility mode SHOULD be emulating this behavior (or it isn't really compatible!) 2) Even if problem #1 isn't resolved, it is unreasonable to expect end-users to manually sync after opening and closing a file. Bottom line, upgrading to Office 2007 killed a feature we rely on in our environment. Office 2003 didn't have this problem. Excel 2007 with native .xlsx doesn't have this problem. It seems to be a bug in Excel 2007's compatibility mode. Thoughts? -Cliff Tag: Calculate average of a range, but exclude errors Tag: 141999
  - 4
    - Aggregating Data in Fifteen Unopened Spreadsheets into Single I have a series of 15 spreadsheets, all having the same column structure. They all have the same filename barring first two letters which refer to a persons initials. The 15 files are stored in the same directory. In that directory I'd like to create a sixteenth file which consolidates much of the data in the 15 files. I want to use that consolidated data in a pivot table. I know that the top-left- hand-corner of the range I want within each of the 15 spreadsheet files is the Cell A10. I cannot be so precise with the bottom right hand corner (BRHC) as the number of rows is variable in each of the fifteen sheets. I know that the BRHC is in column S (if that helps). Additionally, within each of the 15 files there is around 25 rows of data below the BRHC which I do not want to insert into the consolidated file. Can anyone give me pointers as to the easiest way that I can create the sixteenth file, without having any blank rows in that consolidated file. Ideally without the hassle of having to open the 15 files. Thanks. Tag: Calculate average of a range, but exclude errors Tag: 141998
  - 5
    - Value in list wont show same amount of numbers. I have a list of values. For some reason, I cant get one of the values to display the same amount of numbers as the others in the list. I have tried manually changing the cell format, as well as performing the paste special - operation, add procedure. The actual number is:530911.0010 (with the zero on the end) I am performing a vlookup on the list so I need them all uniform. Any clues? 530720.0000 530810.0000 530911.001 530919.0090 530921.4090 540233.6000 540331.0040 Tag: Calculate average of a range, but exclude errors Tag: 141994
  - 6
    - Weird result when printing from macro on one system Maybe someone can help shed some light on this. In Excel 02 (SP3) we have a macro that runs that highlights the selected fields (based on some radial buttons that the user elects) and then it executes: ActiveWindow.SelectedSheets.PrintOut Copies:=2 On 12 of 13 machines it executes perfectly, you get two identical sheets printed out of your default printer. The problem, 1 machine which appears to be setup identical to all the others (including the printer and the print driver) does print the *second* copy correctly. Regardless of the printer the job is sent to (including acrobat writer) the first copy always comes out okay, but the second copy the radial buttons and highlighting get screwed up and you get a somewhat unreadable copy as sheet two. To me it just doesn't make any sense as to why this would happen. But maybe someone here knows just the right tweak to get me back on track. Thank you in advance. Justin Tag: Calculate average of a range, but exclude errors Tag: 141993
  - 7
    - auto fill how do i turn auto fill off? Tag: Calculate average of a range, but exclude errors Tag: 141990
  - 8
    - XL office 2007 file to large I recently created a *relatively large file in excel 2007 and when I tried to save it as a 97-2003 workbook I too was notified that data had been lost. The difference being from what I have read here so far is that the alert message I received informed me that anything after column IV (the original document contains data up to and including column JO) was not saved and this was evidently true. Is there anything I can do to save the entire file as a single 97-2003 workbook without losing anything? Thank you. Tag: Calculate average of a range, but exclude errors Tag: 141987
  - 9
    - Formula Error or Data Error? I have the following spreadsheet which I attempting to calculate the Prior Years Inventory Level in actual Retail $ I have a database query that retrieves the following Information:( DeptDate( week end date(Saturday), OH Retail$, % Chg Prior Year), DeptNum DeptName, CatName (Dept = Abbreviation for Department) DeptDate = (the date(Saturday) week ending (i.e. 5/10/08_ OH Retail$ = The Retail $ of Inventory for that department OH %Chg = % chg from Prior Year (Don't have access to Original Data for a certain Period of Time) Deptnum = Department # DeptName = Department Desc CatName = Category Name (where different sets of deptnums are grouped) The problem I am having is it doesn't seem to cross check the math. I have a pivot table to display the OH$ , I then have 2 cols on the same work sheet that calculates the prior years OH$ but when I cross check it doesn't seem to be right. Col B (Calculated) has the following formula =G3/(1-(C3)*-1) Col C = Contains the OH %CHG for each Dept/Cat for that week COL D (Begins Pivot Table) = Contain Category Name COL E = DeptNum COL F = DeptName COL G (Being Data) = Date on Column headers, and value of Inventory Level The Problem occurs which I cross check the math. COL A Contains =SUM(B3:B13) If I summarize(COL A) the value in the range for each category (All Deptnum in Category A for example it doesn't match) am I doing something wrong Mathematically or is the data possibly bad? See below for data sample The issue is $104,007 with a (21.9)% chg = $133,182 not $126,785 the sum of the individual changes I have confirmed thet query data matches the database data Thanks for any Help A B C D E F G LY OnHand Retail$ DeptDate 5/3/08 Diff Category Dept# DeptDesc 5/3/2008 (row 3) $50,071 -40.90% Consumables 110 Food $29,592 $909 71.90% 111 DSD $1,563 $4,509 -45.40% 114 Speciality Food $2,462 $0 890.00% 115 Frozen Food $43,248 -18.90% 120 HBC $35,074 $0 121 Control Sub $5,110 -8.30% 130 House Keeping $4,686 $7,561 -14.90% 140 Household Chemicals $6,434 $4,811 -14.30% 150 Paper $4,123 $10,081 31.60% 160 Pet $13,266 $6,883 -1.10% 170 Home Organization $6,807 $133,182 $126,785 -21.90% Consumables Total $104,007 Tag: Calculate average of a range, but exclude errors Tag: 141980
  - 10
    - Currency Settings I have created a report using VBA and Excel OLAP cube. Some of the metrics on the report are currency numbers and are to be reported in USD so I have formatted them accordingly using format ( e.g. "$ #,##0.00"). Some of the users in Germany who use decimal (.) as separator between numbers are not able to see the format correctly. I hope they use comma in place of decimal and decimal in place of commas as we normally do. Also other users in Europe see Pound or Euro sign instead of dollar. My report is being used in many geographies and I have to use $ which every body should be able to see. Any suggestions? Tag: Calculate average of a range, but exclude errors Tag: 141978
  - 11
    - Copy formula without changing references [Excel 2007] Hello! In my Workbook I have many cells with data validation settings as follows: Allowed data: List Source: =INDIRECT(L9) Now, I`d like to change the source formula to the following: =IF(M9="P";INDIRECT(L9);R11:R21) When I check the option "Make changes in every cell with the same settings", Excel does it, but it also adjusts the cell references" Is there a way to automatically change the formula in every cell with the same settings, but leaving the formula untouched? The formulas to be changed are not next to each other, they are located throughout the Workbook, in several worksheets, in hundreds of cells, so the copy/paste option wouldn`t make me happy. I tried "Find and Replace", but Excel doesn`t find the data to be changed. I use polish version of the software, so it might be, that some translations I made, are not accurate. Thanks in advance Peter Tag: Calculate average of a range, but exclude errors Tag: 141974
  - 12
    - Data Validation Lookup List from Another File Hi, Without duplicating the cell contents in the current spreadsheet (call it Template), is there anyway to have a dropdown list of items (starting in cell T38 and going down to T45) from another file ([Price_List.xls]License_Only!C2 to C whatever row which will change). Then, when the item is selected from the list in T38, lookup the cell to the left in row D of Price_List.xls and put it in S38. The reason I don't want to duplicate the cell contents in the current spreadsheet is because the file will be way too large to deal with. I hope that explains it well enough Thanks for any help you can offer. Michele Tag: Calculate average of a range, but exclude errors Tag: 141972
  - 13
    - xlsm macros Macros in XL 2007 xlsm spreadsheets that have passwords do not run. Msg says to re-open and enable the macros at the security prompt however the prompt does not have an "enable" option. Searched MS help etc and found a reference indicating that this occurs if a virus s/w pkg does not scan encrypted macros. i use norton 360. Checked with Symantec and they indicate nothing to set/unset in N360 - no answer. problem exists whether N360 is enabled or disabled - makes no difference. If I remove the password the macros work. I'm thinking my only option is to run without the password but perhaps put a digital signature on the spreadsheet. any other ideas? -- regards ...barglen Tag: Calculate average of a range, but exclude errors Tag: 141967
  - 14
    - underline in row not repeating It seems as if my bottom of the cell outline is not repeating on subsequent pages. I have a few header rows on a sheet. The bottom row has a bottom line. I'm talking about cell outlines, not underlining of words in the cell. Anyway, I've told the print option to repeat the first 3 rows on following pages but the bottom outline does not seem to repeat. How can I fix this? Thanks, Keith Tag: Calculate average of a range, but exclude errors Tag: 141960
  - 15
    - zoom preview Is there any way to zoom more than just 2 states (in and out) in an Excel 2003 preview? In so many programs there's a % option but not in Excel. Very sad indeed and very frustrating. I'm wondering if there's some hidden setting that I don't know about. Tag: Calculate average of a range, but exclude errors Tag: 141959
  - 16
    - using RANK I know i can rank a column of numbers from largerst to smallest. Can I use rank for smallest to largest? Any help appreciated. Skip Tag: Calculate average of a range, but exclude errors Tag: 141958
  - 17
    - convert PDF file to excel 2007 file Anyone know how to convert a PDF file to an Excel 2007 file? Need help. Thanks Tag: Calculate average of a range, but exclude errors Tag: 141950
  - 18
    - Setting up questions & answers Any pointers would be most appreciated. I'm wanting to create exams online and have been told Excel will do this. For instance 20 questions with each question having to choose the correct answer from a number of possible answers. I want whoever to go through trhe 20 questions and only know how many they got right by submitting at the end. They won't know which they got right, only the number they got right. Any advice where to start with Excel would be brilliant. Best, Paul B. Tag: Calculate average of a range, but exclude errors Tag: 141946
  - 19
    - Excel newbie needs help from math wiz (poker related) I want to create a Excel workbook that will help in specific poker situations. Short version: I want to be able to calculate how often my opponent will need to fold for an all-in raise to show a positive expectation. Explanation: In poker you can win a hand by having the best hand at the end OR getting your opponent to fold. A common situation comes up where you have a "drawing hand" and face a bet by your opponent. In this situation you know you are currently behind in the hand, but have a chance to win if you catch some of your "outs" (cards that will give you the winning hand). Example: You have one of these drawing hands and it will become the winning hand about 1/3 of the time if you stay in until the end. There is $150 in the pot and your opponent bets another $100. If you were to re-raise all in it would be for a total of $300 ($200 in addition to his $100). x% of the time my opponent will fold in this spot I want to be able to solve for x (the amount my opponent will need to fold) to show me the break even point of this re-raise. When I re-raise all in he is only left with two options so we need to account for both. He can call or he can fold: 100% of the time he folds I will win $250 2/3 of the time he calls I miss my draw and lose $300 (the amount I re-raised all in with) 1/3 of the time I will hit my draw and win $450 ($150 in the original pot+his $100 bet+his $200 call of my all in re-raise) So, how can I create an excel workbook/calculation that will calculate the % my opponent will need to fold for my all in re-raise to break even? Thanks in advance for any help with this. Tag: Calculate average of a range, but exclude errors Tag: 141945
  - 20
    - HLOOKUP-ish? I'm stumped. I know you can't do an HLOOKUP if you're not starting at the top of the range. I have a worksheet (called "Data") that contains 3500 rows of data. There are multiple "departments" within this data. I want to be able to look up a specific "department" and return the result in the row beneath it (on the worksheet "Results"), even if the department isn't the first row. Here's an example: From the "Data" worksheet: 1. 2008149Bob 2. 2008149Donna 3. 2008149Annalisa 4. 2008149Lindy 5. 2008153Total 6. 2008153Joe 7. 2008153Marty 8. 2008153Larry 9. 2008153Anne 10. 2008153Owen 11. 2008153William 12. 2008153Amy 13. 2008153Jim 14. 2008153Nina 15. 2008153Thad 16. 2008153Richard 17. 2008153Erica On a different worksheet ("Results", I want to look up Department 153's Total (row 5) and return the results from the row beneath it (in this case, "2008153Joe"). Because the departments aren't always in the same range (sometimes Dept 153 will be in row 5, sometimes in row 116, etc), I can't give the range a 'set' number. Is this possible? Tag: Calculate average of a range, but exclude errors Tag: 141934
  - 21
    - Personal Macro Workbook Must Stay Open For Recording I had the same problem. I solved the problem by opening Excel and save as a blank workbook to the following Directory: C:\Program Files\Microsoft Office\OfficeXX\XLSTART -where XX = Office version (probably 10,11, or 12) Your directory might change if you did a custom install. Name the file Personal.xls Cheers Tag: Calculate average of a range, but exclude errors Tag: 141929
  - 22
    - Average of every 18th cell in a column Hello! My spreadsheet looks like this: Column D value1 value2 value3 ... Average1 of 1,2,3... (cell D25) value4 value5 value6 ... Average2 of 4,5,6... (cell D43)... Every 18th row, starting from D25 there is a "partial" average. Now, I want to calculate the total average from them, but... It is a template, so there actually is a value in cell D25 and D43 (25+18), but in the following cells, there only is the formula for average calculation of future entries. In cells D61 and further, there is an error message (dividing by 0). What would be the formula to calculate an average from every 18th cell, starting with D25, ending by, say, D2000, and considering only the cells with an actual value? Thanks in advance! Peter Tag: Calculate average of a range, but exclude errors Tag: 141928
  - 23
    - Data Validation I want to use data validation on a cell where the user MUST enter any four digits, leading zeros are permitted. Can someone explain how I do this? Tag: Calculate average of a range, but exclude errors Tag: 141923
  - 24
    - strip characters from a "number" like string Hello Could someone advise as to how to strip characters from a formula resulting string which could look like the following? ( some of the result would actually be numbers while others are number/character combo's - see below for examples) 0002 0124 0125-A 1566A-A Thank you for any assistance you can offer. ! Tag: Calculate average of a range, but exclude errors Tag: 141920
  - 25
    - Sum Unique Values in a Pivot Table Hello I have a spreadsheet with several records for each machine's name as illustrated below: Server Name / Database Instance Name / CPUs / Concurrent Access ______________________________________________________________ Larry / Larry1 / 8 / 5 Larry / Larry2 / 8 / 50 Larry / Larry3 / 8 / 10 Larry / Larry4 / 8 / 1 Amy / Amy1 / 2 / 10 Amy / Amy2 / 2 / 5 Amy / Amy3 / 2 / 35 Larry / Larry1 / 16 / 500 Derron / Derron / 6 / 25 I want to have pivot tables with "Server Name" in a ROW and two DATA fields: - Data field 1 : a sum of the total "Concurrent Access" for all database instances on the Server (i.e. 5+50+10= 65 for Larry) - Data field 2 : a sum of "CPUs" per unique occurrences of Server Name (i.e. 8 for Larry and not 8+8+8) Is there some way to do that in a pivot table? Thanks Tag: Calculate average of a range, but exclude errors Tag: 141914
- Next
  - 1
    - Custom locking of columns and panes [Excel 2007] Hello! Is it possible to lock columns and panes so that they NEVER move? Normally, locked panes remain visible when scrolling up and down, while locked columns remain visible when scrolling right and left. I`d like to have the first 8 columns and the first 5 panes visible simultaneously even when I go to, say, cell VX12345. And a second question: Is it possible to lock a number of randomly selected columns? For example the 1st, 5th and 8th column. Greetings Peter Tag: Calculate average of a range, but exclude errors Tag: 141913
  - 2
    - Formula Error - Assign number to unique values. I have a list of codes in F$19:F$1500. I was attempting to use the following code to assign a number to each unique value in the list and repeat the previously assigned number if it is duplicated. This works for the first 15-20 numbers, but returns an error (#n/a) afterwards as if the code stopped working. I have tried EVERYTHING... Same result each time. =IF(COUNTIF($F$18:F19,F19)>1,VLOOKUP(F19,F$18:G18,2,FALSE),MAX($G $18:G18)+1) Any clues? Tag: Calculate average of a range, but exclude errors Tag: 141902
  - 3
    - Filter on non-blanks in a range (multiple columns) I have a spreadsheet that has offers on parts from multiple clients. The spreadsheet is 12,000 lines long. I am looking to select on a range (of offers) and the highest offer gets put into a BEST OFFER column on the same row. Example Part Number BestOffer Offer#1 Offer#2 Offer#3 Offer#4 Offer#5 xxxxxx-001 $13.00 $1.00 $5.25 $10.00 $3.50 $13.00 xxxxxx-002 $5.00 $1.25 $3.00 $5.00 $4.75 $8.00 The spreadsheet (12,000 lines long) has to remain in the same, original format as it came in BEFORE I received these offers. What is the best bet to address this? Tag: Calculate average of a range, but exclude errors Tag: 141900
  - 4
    - Cannot include "[" or "]" in filename path??? When I try to save an Excel spreadsheet to My Document/[Activities]/2008-05-05 I recieve an error that some characters are not allowed. [ and ] are both valid characters for a file name. What can I do to get Excel to accept these characters at the SaveAs dialog box? Tag: Calculate average of a range, but exclude errors Tag: 141897
  - 5
    - Separate at first number Have a string of codes similar to below. Any way to separate (LEFT, or RIGHT) all text to the right of the first number in the cell? (Some codes have more/less characters before and after zero, so using right or left by itself may not work.) apples12havocx cherries84toughx plums45easyxx Tag: Calculate average of a range, but exclude errors Tag: 141892
  - 6
    - find duplicates and add some data I've got two Excel sheets containing data about employees. Sheet A is a complete list, and Sheet B is a shorter list with only employees who are in a certain job category. I need to compare the two lists--both have Employee ID numbers as part of the data set. Where Excel finds a matching ID #, I would like to append the job title that appears in Sheet B into a blank column on Sheet A. I'm thinking this could be done with some combo of an IF statement and VLOOKUP, but I haven't gotten any further than that. Any help would be greatly appreciated! Tag: Calculate average of a range, but exclude errors Tag: 141891
  - 7
    - Not reading pasted values? I have had this problem a few times before and I haven't been able to find the answer... I had a list of formulas and results in the column next to it (B). I copied the values into another list (C) and now am referring another set of formulas (d) to point at the pasted results. The new formula's dont work (#N/A - error) unless I go down to each pasted value and click in that cell and hit enter. It's like saying that the cell values aren't real until I validate them by hitting enter in those cells? Tag: Calculate average of a range, but exclude errors Tag: 141888
  - 8
    - Excel data to Word for Mail merge --____VIGFOREMMRBSJAZWDOXY____ Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Tue, 5 May 2008 05:45:46 -0500" SSBoYXZlIGNyZWF0ZWQgYW4gZXhjZWwgZGF0YWJhc2UgdGhhdCBsaW5rcyB0byB3b3JkIHRvIGJl IHVzZWQgZm9yIGEgbWFpbCBtZXJnZS4gRm9yIHRoZSBtb3N0IHBhcnQgaXQgd29ya3MgZmluZSwg aG93ZXZlciBpIGhhdmUgY2hlY2sgYm94ZXMgaW4gbXkgbGV0dGVyIGZvciB3b3JkIGFuZCBJIHdh cyB3b25kZXJpbmcgaG93IEkgY291bGQgaW5jbHVkZSBkYXRhIGZyb20gZXhjZWwgdGhhdCBsaW5r cyB0byB0aGUgY2hlY2sgYm94ZXM/DQoNClRoYW5rcywNCkRhdmlk --____VIGFOREMMRBSJAZWDOXY____ Content-Type: multipart/related; boundary="____NMMOTADINYBDTVAZJAWH____" --____NMMOTADINYBDTVAZJAWH____ Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: base64 Content-Disposition: inline; modification-date="Tue, 5 May 2008 05:45:46 -0500" PEhUTUw+PEhFQUQ+DQo8TUVUQSBodHRwLWVxdWl2PUNvbnRlbnQtVHlwZSBjb250ZW50PSJ0ZXh0 L2h0bWw7IGNoYXJzZXQ9dXRmLTgiPg0KPE1FVEEgY29udGVudD0iTVNIVE1MIDYuMDAuMjkwMC4z MzE0IiBuYW1lPUdFTkVSQVRPUj48L0hFQUQ+DQo8Qk9EWSBzdHlsZT0iTUFSR0lOOiA0cHggNHB4 IDFweDsgRk9OVDogMTBwdCBUYWhvbWEiPg0KPERJVj5JIGhhdmUgY3JlYXRlZCBhbiBleGNlbCBk YXRhYmFzZSB0aGF0IGxpbmtzIHRvIHdvcmQgdG8gYmUgdXNlZCBmb3IgYSBtYWlsIG1lcmdlLiBG b3IgdGhlIG1vc3QgcGFydCBpdCB3b3JrcyBmaW5lLCBob3dldmVyIGkgaGF2ZSBjaGVjayBib3hl cyBpbiBteSBsZXR0ZXIgZm9yIHdvcmQgYW5kIEkgd2FzIHdvbmRlcmluZyBob3cgSSBjb3VsZCBp bmNsdWRlIGRhdGEgZnJvbSBleGNlbCB0aGF0IGxpbmtzIHRvIHRoZSBjaGVjayBib3hlcz88L0RJ Vj4NCjxESVY+Jm5ic3A7PC9ESVY+DQo8RElWPlRoYW5rcyw8L0RJVj4NCjxESVY+RGF2aWQ8L0RJ Vj48L0JPRFk+PC9IVE1MPg== --____NMMOTADINYBDTVAZJAWH____-- --____VIGFOREMMRBSJAZWDOXY____-- Tag: Calculate average of a range, but exclude errors Tag: 141887
  - 9
    - Automatic, conditional chart creation from separate files [Excel I have a number of separate files (Excel 2007). Each file contains several sheets with test results of a few students. The students (the same, all the time) are evaluated each month using the same test, and each month a new sheet is created for each of them. For example: Workbook 1: Worksheet A (John), Worksheet B (Jennifer), Worksheet C (Anna) Workbook 2 (a month later): Worksheet A (Jennifer), Worksheet B (John), Worksheet C (Anna) I`d like to create a separate file with charts depicting the mean results for each student. The chart would retrieve data automatically from every file stored in a particular folder (including files to be created later), but in the following way: The chart for John: Retrive data from every file in a given folder, from a given range, but only if the worksheet contains "John" in L2. In this case, Excel would create a separate file with three charts, retrieving data from Workbook 1 AND 2, but from worksheet A OR B OR C. Is that possible? Could anybody help with a VB Script or Macro? I don`t think it`s possible without programming, which I can`t at all, is it? Thanks in advance Peter, Poland Tag: Calculate average of a range, but exclude errors Tag: 141884
  - 10
    - Lookup Hello All, I would like to use lookup function. Usually the Vlookup would lookup the first instance the lookup value comes across. eg data set. Name Container Type --------- -------------- --------- John A 34 John B 45 John C 44 Peter A 67 Peter B 77 Now if I do a look up for John and Peter, the unique value would be always 34 and 67 Now suppose I have another sheet where the type is requried as a function then it would always use 34 and 67 for John and Peter. How do I use a Vlookup with 2 conditions ie If Name is John and Container is A then it would use 34, else it would us John and B . Would appreciate any help. thanks SD Tag: Calculate average of a range, but exclude errors Tag: 141882
  - 11
    - Chart from separate workbooks Hello! I`m new to this group so greetings for everybody :). My questions (MS Excel 2007): There are 10 separate files, each containing a similar worksheet. There is an eleventh, cumulative file, containing a chart from these ten files. The number of files grows constantly. I would like the charts in the cumulative file to change, based on the newly created files. How to do this in a possibly automatic manner? For example: 5 persons are evaluated each month with a 10-question test and get a grade (from 1 to 5)for each question. In the cumulative file there is a chart depicting the mean grade for each person and each question, calculated from previous evaluation charts. I would like the cumulative chart to change with the creation of each new evaluation chart, which is placed in the same folder. Thanks in advance Peter Tag: Calculate average of a range, but exclude errors Tag: 141880
  - 12
    - Hot Shop Reverso Grande GMT - Jaeger LeCoultre Minimum Price Hot Shop Reverso Grande GMT - Jaeger LeCoultre Minimum Price Hot Watch Shop : http://www.hotwatchshop.com Jaeger LeCoultre Watches : http://www.hotwatchshop.com/Jaeger-LeCoultre-Watches.html Reverso Grande GMT : http://www.hotwatchshop.com/Reverso-Grande-GMT-Watch.html Reverso Grande GMT All Products : Jaeger LeCoultre Reverso Grande GMT 18kt Rose Gold Mens Watch 302.21.20 : http://www.hotwatchshop.com/Jaeger-LeCoultre-wristwatch-4242.html Jaeger LeCoultre Reverso Grande GMT 18kt Rose Gold Mens Watch 302.24.20 : http://www.hotwatchshop.com/Jaeger-LeCoultre-wristwatch-4243.html The Same Jaeger LeCoultre Watches Series : Master Compressor : http://www.hotwatchshop.com/Master-Compressor-Watch.html Master Control : http://www.hotwatchshop.com/Master-Control-Watch.html Master Eight Days : http://www.hotwatchshop.com/Master-Eight-Days-Watch.html Master Geographic : http://www.hotwatchshop.com/Master-Geographic-Watch.html Master Hometime : http://www.hotwatchshop.com/Master-Hometime-Watch.html Master Ultra Thin : http://www.hotwatchshop.com/Master-Ultra-Thin-Watch.html Reverso Duo : http://www.hotwatchshop.com/Reverso-Duo-Watch.html Reverso Gran'Sport : http://www.hotwatchshop.com/Reverso-GranSport-Watch.html Reverso Grande Automatique : http://www.hotwatchshop.com/Reverso-Grande-Automatique-Watch.html Reverso Grande Date : http://www.hotwatchshop.com/Reverso-Grande-Date-Watch.html Reverso Grande GMT : http://www.hotwatchshop.com/Reverso-Grande-GMT-Watch.html Reverso Grande Reserve : http://www.hotwatchshop.com/Reverso-Grande-Reserve-Watch.html Reverso Grande Taille : http://www.hotwatchshop.com/Reverso-Grande-Taille-Watch.html Reverso Reserve de Marche : http://www.hotwatchshop.com/Reverso-Reserve-de-Marche-Watch.html Women's Watches : http://www.hotwatchshop.com/Womens-Watches-Watch.html Tag: Calculate average of a range, but exclude errors Tag: 141879
  - 13
    - Hot Shop Orient Watches Day Date - Orient Minimum Price Hot Shop Orient Watches Day Date - Orient Minimum Price Hot Watch Shop : http://www.hotwatchshop.com Orient Watches : http://www.hotwatchshop.com/Orient-Watches.html Orient Watches Day Date : http://www.hotwatchshop.com/Orient-Watches-Day-Date-Watch.html Orient Watches Day Date All Products : Orient Pepsi Automatic Dive Watch : http://www.hotwatchshop.com/Orient-wristwatch-5695.html Orient Cem60001b Automatic Day Date Mens Watch : http://www.hotwatchshop.com/Orient-wristwatch-5696.html Orient Cem60001w Automatic Day Date Mens Watch : http://www.hotwatchshop.com/Orient-wristwatch-5697.html Orient Cem60001h Automatic Day Date Mens Watch : http://www.hotwatchshop.com/Orient-wristwatch-5698.html Orient Cfnaa006b Automatic Day Date Mens Watch : http://www.hotwatchshop.com/Orient-wristwatch-5699.html Orient Cfpaa002b Automatic Day Date Mens Watch : http://www.hotwatchshop.com/Orient-wristwatch-5700.html The Same Orient Watches Series : Orient Titanium Chronograph : http://www.hotwatchshop.com/Orient-Titanium-Chronograph-Watch.html Orient Watches Chronograph : http://www.hotwatchshop.com/Orient-Watches-Chronograph-Watch.html Orient Watches Day Date : http://www.hotwatchshop.com/Orient-Watches-Day-Date-Watch.html Orient Watches Diver : http://www.hotwatchshop.com/Orient-Watches-Diver-Watch.html Orient Watches M-Force : http://www.hotwatchshop.com/Orient-Watches-M-Force-Watch.html Orient Watches Military : http://www.hotwatchshop.com/Orient-Watches-Military-Watch.html Orient Watches Monthly Calender : http://www.hotwatchshop.com/Orient-Watches-Monthly-Calender-Watch.html Orient Watches Multi Eye : http://www.hotwatchshop.com/Orient-Watches-Multi-Eye-Watch.html Orient Watches Open Heart : http://www.hotwatchshop.com/Orient-Watches-Open-Heart-Watch.html Orient Watches Power Reserve : http://www.hotwatchshop.com/Orient-Watches-Power-Reserve-Watch.html Orient Watches Retro Future : http://www.hotwatchshop.com/Orient-Watches-Retro-Future-Watch.html Orient Watches Skeleton : http://www.hotwatchshop.com/Orient-Watches-Skeleton-Watch.html Orient Watches Sport Auto : http://www.hotwatchshop.com/Orient-Watches-Sport-Auto-Watch.html Orient Watches Wide Calender : http://www.hotwatchshop.com/Orient-Watches-Wide-Calender-Watch.html Orient Watches World Time : http://www.hotwatchshop.com/Orient-Watches-World-Time-Watch.html Orient Women's Watches : http://www.hotwatchshop.com/Orient-Womens-Watches-Watch.html Tag: Calculate average of a range, but exclude errors Tag: 141878
  - 14
    - problems when saving as comma delimited text file Using Excel 2007. I'm having problems trying to save a worksheet as a text file, when the worksheet contains text like : 329300041118 . Steps to reproduce : 1) open excel (you should then be looking at an empty spreadsheet) 2) into cell 1A, paste the text : 329300041118 . The cell should display : 3.293E+11 . 3) Save As -> Other Formats -> CSV comma delimited When you look at the CSV file in a text editor (e.g. notepad), it shows : 3.293E+11 What I want to see is : 329300041118 I realise I can "fix" this by using "Format Cells" to control the precision, but is there some way of getting this to work without having to set this on a spreadsheet by spreadsheet basis ? i.e. Is there some way to get excel to export the straight text of the data, without it trying to round what it interprets as numeric values at a global excel level ? TIA Tag: Calculate average of a range, but exclude errors Tag: 141876
  - 15
    - Hot Shop Patek Philippe Gondolo Watches - Patek Philippe Minimum Hot Shop Patek Philippe Gondolo Watches - Patek Philippe Minimum Price Hot Watch Shop : http://www.hotwatchshop.com Patek Philippe Watches : http://www.hotwatchshop.com/Patek-Philippe-Watches.html Patek Philippe Gondolo Watches : http://www.hotwatchshop.com/Patek-Philippe-Gondolo-Watches-Watch.html Patek Philippe Gondolo Watches All Products : Patek Philippe Gondolo 18kt White Gold Mens Watch 5109G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10301.html Patek Philippe Gondolo 18kt Rose and White Gold Mens Watch 5099RG : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10302.html Patek Philippe Gondolo 18kt Yellow Gold Mens Watch 5109J : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10303.html Patek Philippe Gondolo 18kt Rose Gold Mens Watch 5111R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10304.html Patek Philippe Gondolo Platinum and 18kt Rose Gold Mens Watch 5111PR : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10305.html Patek Gondolo Gemma White Gold Ladies Watch 4991G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10306.html Patek Gondolo Gemma White Gold Ladies Watch 4981G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10307.html Patek Gondolo Gemma Rose Gold Ladies Watch 4991R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10308.html Patek Philippe Gondolo 18kt White Gold Mens Watch 5489G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10403.html Patek Philippe Gondolo 18kt Rose Gold Mens Watch 5489R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10404.html Patek Philippe Gondolo 18kt Yellow Gold Mens Watch 5010J : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10405.html Patek Philippe Gondolo 18kt White Gold Black Mens Watch 5010G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10406.html Patek Philippe Gondolo 18kt White Gold Diamond Mens Watch 5025G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10407.html Patek Philippe Gondolo 18kt White Gold Mens Watch 5111G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10408.html Patek Philippe Gondolo 18kt White Gold Diamond Blue Mens Watch 5112G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10409.html Patek Philippe Gondolo Platinum Mens Watch 5109P : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10410.html Patek Philippe Gondolo 18kt Yellow Gold Mens Watch 5111J : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10411.html Patek Philippe Gondolo Serata 18kt White Gold Diamond Blue Ladies Watch 4972G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10412.html Patek Philippe Gondolo 18kt White Gold Pearl Bracelet Diamond Ladies Watch 4868G-1G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10413.html Patek Philippe Gondolo 18kt White Gold 3-Strap Diamond Ladies Watch 4869G-1G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10414.html Patek Philppe Gondolo 18kt Rose Gold Vanilla Strap Diamond Ladies Watch 4868R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10415.html Patek Philippe Gondolo Gemma 18kt White Gold Diamond Rose Ladies Watch 4992G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10416.html Patek Philppe Gondolo 18kt White Gold Taupe Strap Diamond Ladies Watch 4868G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10417.html Patek Philippe Gondolo 18kt Rose Gold Pearl Bracelet Diamond Ladies Watch 4868G-1R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10418.html Patek Philippe Gondolo Serata 18kt White Gold Diamond Ladies Watch 4973G : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10419.html Patek Philippe Gondolo 18kt Rose Gold Mens Watch 5109R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10420.html Patek Gondolo Gemma Rose Gold Ladies Watch 4981R : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10421.html Patek Complicated Gondolo Calendario Yellow Gold Men's Watch 5135J : http://www.hotwatchshop.com/Patek-Philippe-wristwatch-10422.html The Same Patek Philippe Watches Series : Patek Philippe Twenty~4 Watches : http://www.hotwatchshop.com/Patek-Philippe-Twenty4-Watches-Watch.html Patek Philippe Grand Complications Watches : http://www.hotwatchshop.com/Patek-Philippe-Grand-Complications-Watches-Watch.html Patek Philippe World Time Watches : http://www.hotwatchshop.com/Patek-Philippe-World-Time-Watches-Watch.html Patek Philippe Complicated Timepieces Watches : http://www.hotwatchshop.com/Patek-Philippe-Complicated-Timepieces-Watches-Watch.html Patek Philippe Aquanaut Watches : http://www.hotwatchshop.com/Patek-Philippe-Aquanaut-Watches-Watch.html Patek Philippe Calatrava Watches : http://www.hotwatchshop.com/Patek-Philippe-Calatrava-Watches-Watch.html Patek Philippe Gondolo Watches : http://www.hotwatchshop.com/Patek-Philippe-Gondolo-Watches-Watch.html Patek Philippe Golden Ellipse Watches : http://www.hotwatchshop.com/Patek-Philippe-Golden-Ellipse-Watches-Watch.html Tag: Calculate average of a range, but exclude errors Tag: 141873
  - 16
    - Hot Shop Omega Speedmaster Broad Arrow - Omega Minimum Price Hot Shop Omega Speedmaster Broad Arrow - Omega Minimum Price Hot Watch Shop : http://www.hotwatchshop.com Omega Watches : http://www.hotwatchshop.com/Omega-Watches.html Omega Speedmaster Broad Arrow : http://www.hotwatchshop.com/Omega-Speedmaster-Broad-Arrow-Watch.html Omega Speedmaster Broad Arrow All Products : Omega Speedmaster Broad Arrow Olympic Edition Timeless Collection Mens Watch 3557.50 : http://www.hotwatchshop.com/Omega-wristwatch-5540.html Omega Speedmaster Mens Broad Arrow 3551.20 : http://www.hotwatchshop.com/Omega-wristwatch-5541.html Omega Men's Speedmaster Broad Arrow Automtatic Watch #3556.50.00 : http://www.hotwatchshop.com/Omega-wristwatch-5542.html Omega Speedmaster Broad Arrow Rattrapante Mens Watch 3582.31 : http://www.hotwatchshop.com/Omega-wristwatch-5543.html The Same Omega Watches Series : Omega Constellation Double Eagle : http://www.hotwatchshop.com/Omega-Constellation-Double-Eagle-Watch.html Omega Constellation Quadra : http://www.hotwatchshop.com/Omega-Constellation-Quadra-Watch.html Omega Seamaster Aqua Terra : http://www.hotwatchshop.com/Omega-Seamaster-Aqua-Terra-Watch.html Omega Seamaster Planet Ocean : http://www.hotwatchshop.com/Omega-Seamaster-Planet-Ocean-Watch.html Omega Speedmaster Broad Arrow : http://www.hotwatchshop.com/Omega-Speedmaster-Broad-Arrow-Watch.html Omega Watches Constellation : http://www.hotwatchshop.com/Omega-Watches-Constellation-Watch.html Omega Watches De Ville : http://www.hotwatchshop.com/Omega-Watches-De-Ville-Watch.html Omega Watches Seamaster : http://www.hotwatchshop.com/Omega-Watches-Seamaster-Watch.html Omega Watches Seamaster 300M : http://www.hotwatchshop.com/Omega-Watches-Seamaster-300M-Watch.html Omega Watches Seamaster Apnea : http://www.hotwatchshop.com/Omega-Watches-Seamaster-Apnea-Watch.html Omega Watches Speedmaster : http://www.hotwatchshop.com/Omega-Watches-Speedmaster-Watch.html Omega Constellation My Choice Watches : http://www.hotwatchshop.com/Omega-Constellation-My-Choice-Watches-Watch.html Omega Constellation Watches : http://www.hotwatchshop.com/Omega-Constellation-Watches-Watch.html Omega Double Eagle Watches : http://www.hotwatchshop.com/Omega-Double-Eagle-Watches-Watch.html Omega DeVille Prestige Watches : http://www.hotwatchshop.com/Omega-DeVille-Prestige-Watches-Watch.html Omega Seamaster Watches : http://www.hotwatchshop.com/Omega-Seamaster-Watches-Watch.html Omega Speedmaster Day Watches : http://www.hotwatchshop.com/Omega-Speedmaster-Day-Watches-Watch.html Omega Speedmaster Legend Watches : http://www.hotwatchshop.com/Omega-Speedmaster-Legend-Watches-Watch.html Omega Speedmaster Reduced Watches : http://www.hotwatchshop.com/Omega-Speedmaster-Reduced-Watches-Watch.html Tag: Calculate average of a range, but exclude errors Tag: 141872
  - 17
    - Opening Excel causes 70 worksheets to open When I click on the icon Excel opens and then begins to open 70 worksheets. After 70 worksheets are open it stops. It seems to be normal after that except for the 70 open worksheets. Help? Tag: Calculate average of a range, but exclude errors Tag: 141866
  - 18
    - Bizarre Excel 2007 behavior solved I have a spreadsheet created in Excel 2003 that has two columns with conditional formatting such that if the entry is zero no data appears in those columns. There is no conditional formatting in any other columns of this spreadsheet that has many embedded formulae. When that spreadsheet is opened in Excel 2007, whether or not it is converted to the 2007 file structure or used in compatibility mode as a 2003 format spreadsheet, Excel 2007 does not let me see data being entered into any cell as I type. I can see the key strokes appearing in the formula bar but the cursor remains in the cell. If the data entered is one where auto-complete applies then part of the cell turns black. This spreadsheet behaves this way in Excel 2007 run under XP or Vista and regardless of whether the cell has any or no embedded formulae. The only way to correct this is to turn off conditional formatting for the entire sheet, even though only two columns have any conditional formatting in the entire spreadsheet. I can then reapply the conditional formatting to those two columns and everything seems to work as it should. This holds true whether I maintain the spreadsheet in 2003 format or convert to 2007 file format. It took me more time than it was worth to figure that out with no help from Microsoft. I want to congratulate the geniuses who designed the Ribbon of Abomination for gifting me this additional annoyance in Excel 2007. I would also like to note that on every computer I have used, ranging from single to Quad core machines, Excel 2007 opens and performs disk access operations notably slower than Excel 2003 in both XP and Vista, the latter imposing time by your wristwatch performance penalties of its own. I presume the geniuses who programmed Vista gifted Office 2007 with the fruits of their misbegotten labors. If Microsoft cannot deliver performance or usability what advantage is there to using local Excel over using web based applications? With the Ribbon of Abomination Microsoft has destroyed familiarity with the product as reason for users to keep using Excel. I cannot be alone in looking at other options. Tag: Calculate average of a range, but exclude errors Tag: 141861
  - 19
    - question and answerwith Excel Any pointers would be most appreciated. I'm wanting to create exams online and have been told Excel will do this. For instance 20 questions with each question having to choose the correct answer from a number of possible answers. I want whoever to go through trhe 20 questions and only know how many they got right by submitting at the end. They won't know which they got right, only the number they got right. Any advice where to start with Excel would be brilliant. Best, Paul B. Tag: Calculate average of a range, but exclude errors Tag: 141857
  - 20
    - Wrap Text and Blank Lines I have a VBA script in Excel that fills data from one spreadsheet to another. In the output file some of the cells are having the formatting .WrapText = True. Now I have the strange situation that some cells display half a line or an entire line of white spaces at the end, even though in the text no blank or special characters are present. I'd like to get rid of the white space or at least understand why this is happening. I have looked at the size of the cells, the number of characters but I have not yet found the trigger for the phenomenon. I have been searching quite some time now and everybody seems to have the same issue. Unfortunately I have not yet found the solution. That's why I ask again. Any ideas are welcome mat Tag: Calculate average of a range, but exclude errors Tag: 141848
  - 21
    - Change Uppercase to Lowercase I have a spread sheet created with Excel 2007. One colum has first and last names in all caps. How can I convert the first letter for each name to be caps and the rest of the letters to be lowercase? I want to do the whole colum at once. Thanks -- ---------------------------------------------------- This mailbox protected from unsolicited email by Spam Alarm from Dignity Software http://www.dignitysoftware.com Tag: Calculate average of a range, but exclude errors Tag: 141844
  - 22
    - Excel 2007 - file double clicked in Win Explorer opens Application, but not workbook I have had Excel 2007 for a little over a year. A few weeks ago, I noticed that when I double-clicked on an Excel spreadsheet from within Windows Explorer, Excel opened but the workbook did not. Neither did the "Book1" workbook. It looked just as Excel would look if you opened it and then exited the workbook, leaving the application open. If Excel is opened in this manner and then I double-click a second time on the workbook, then it opens. I do not think that this is related to SP1, but SP1 was automatically installed on 4/11/08. I cannot recall if this problem cropped up before that date, but I think that it did and it does seem unlikely that a service pack update would cause such an obvious error. Does anybody have any ideas as to what could cause such a problem? Thanks in advance. Alan Tag: Calculate average of a range, but exclude errors Tag: 141840
  - 23
    - import stock prices I keep trying to import an individual stock price to a single cell but the Data/Import External Data/New Web Query system seems to only allow me to only import a whole table which of course changes my whole spread sheet. Any suggestions on how to just import only the current stock price to a single cell? Ron Patterson Tag: Calculate average of a range, but exclude errors Tag: 141829
  - 24
    - Capturing data I am working on a registration form for a meeting. I currently have a sheet set up as a registration form with cells for various fees to be paid by the registrant. My current plan is to use a seperate sheet for each registrant (approx 100 - 150) all within the same workbook. The question is: What would be the best way to summarize the various amounts paid and the numbers of people registered for each event all onto one sheet? I looked at using a Pivot Table, but apparently my data on the registration form is not arranged correctly. I have been looking at using a summary sheet within the same workbook, but have not been able to come up with a formula that will add up the numbers from all of the sheets. Thanks for any help. Charlie Tag: Calculate average of a range, but exclude errors Tag: 141825
  - 25
    - Problem with conversions There are problems with Exel 2007 converting to 2003 where worksheet are missing, math is done wrong, numbers magically appear in formulas and other numbers seem to change in the conversion. From what I understand, this may be restricted to educatioinal versions given to schools. From some of the other blogs, I gather that 2007 is becoming a bit of a pain. There should be a fix for this from the big and mighty Microsoft. Apparently, a lot of students and faculty are fuming! Tag: Calculate average of a range, but exclude errors Tag: 141821

Hello!

In my workbook I have several worksheets. In cell B2 of every worksheet,
there is an average from the data in that worksheet. As this is a
template, some data is not entered yet, and some B2 cells show error
(div by 0). Now, I wanted to calculate the average from B2 cells of all
worksheets, but exclude all cells with errors.

My formula

=Average(Sheet1:Sheet12!B2)

How do I modify this to work?

Thakks
Peter

Top

Re: Calculate average of a range, but exclude errors by Niek

Niek
Thu May 08 05:29:24 CDT 2008

Hi Peter,

Change the formulas of the cells that you want to average to

=IF(ISERROR(YourFormula),"",YourFormula)

or, even better, check for the divider being zero and generate "" if that is the case.

The AVERAGE() function ignores the "" cells.
--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Piotr" <erazm79WieszCoZrobic@tlen.pl> wrote in message news:fvu9ft$7oa$1@inews.gazeta.pl...
| Hello!
|
| In my workbook I have several worksheets. In cell B2 of every worksheet,
| there is an average from the data in that worksheet. As this is a
| template, some data is not entered yet, and some B2 cells show error
| (div by 0). Now, I wanted to calculate the average from B2 cells of all
| worksheets, but exclude all cells with errors.
|
| My formula
|
| =Average(Sheet1:Sheet12!B2)
|
| How do I modify this to work?
|
| Thakks
| Peter

Top

RE: Calculate average of a range, but exclude errors by GarysStudent

GarysStudent
Thu May 08 05:41:04 CDT 2008

See Rosenfeld's comments in :

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_thread/thread/939b06ebd6a6b561/1f1ff77e04a97858?hl=en&lnk=st&q=average+exclude+errors+%3A*Excel

and adapt to your needs.
--
Gary''s Student - gsnu2007h

"Piotr" wrote:

> Hello!
>
> In my workbook I have several worksheets. In cell B2 of every worksheet,
> there is an average from the data in that worksheet. As this is a
> template, some data is not entered yet, and some B2 cells show error
> (div by 0). Now, I wanted to calculate the average from B2 cells of all
> worksheets, but exclude all cells with errors.
>
> My formula
>
> =Average(Sheet1:Sheet12!B2)
>
> How do I modify this to work?
>
> Thakks
> Peter
>

Top