Averaging cells not connected

Iam somewhat new to Excell 2003.  I've just learned how to average cells (& 
ignore zeros) that are connected, either row to row, or coloumb to coloumb.  
I need to know how to average cells (& ignore zeros or blank cells) that is a 
mix of connected cell and others that are not connected.....like;
B5    C5     D5
B10  C10   D10  
this format continues for several more rows.

Not sure if I'll find this help screen again.  If  you don't mined, would 
you also email the results to:   cusjan@yahoo.com        Thanks.
0
Gary103 (266)
10/19/2008 11:36:01 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
606 Views

Similar Articles

[PageSpeed] 27

Try:

=AVERAGE(IF(B5:D5<>0,B5:D5),IF(B10:D10<>0,B10:D10))

This is an array formula so must be enterered by pressing and holding the 
Crtl & Shift keys while you press the Enter key.  If you do this correctly 
then Excel will place curly braces { } around the formula.

If you do not array enter it you will get a #Value! error.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Gary" <Gary@discussions.microsoft.com> wrote in message 
news:4D4D6EDE-E790-4867-98CE-6DCED1708F78@microsoft.com...
> Iam somewhat new to Excell 2003.  I've just learned how to average cells 
> (&
> ignore zeros) that are connected, either row to row, or coloumb to 
> coloumb.
> I need to know how to average cells (& ignore zeros or blank cells) that 
> is a
> mix of connected cell and others that are not connected.....like;
> B5    C5     D5
> B10  C10   D10
> this format continues for several more rows.
>
> Not sure if I'll find this help screen again.  If  you don't mined, would
> you also email the results to:   cusjan@yahoo.com        Thanks.
> 


0
sandymann2 (1054)
10/19/2008 12:50:55 PM
Hi,

First point - blank cells are treated are ignored by the average function, 
as are all non-numeric cells.

Here is one formula:
=AVERAGE((B5:D5<>0)*B5:D5,(B10:D10<>0)*B10:D10)
It needs to be array entered - Press Shift+Ctrl+Enter rather than Enter.

Now if the rows between B5:D5 and B10:D10 are blank, or contain non-numeric 
data you can use:
=AVERAGE(B5:D10)
No array entry needed.

If this helps, please click the Yes button.
-- 
Thanks,
Shane Devenshire


"Gary" wrote:

> Iam somewhat new to Excell 2003.  I've just learned how to average cells (& 
> ignore zeros) that are connected, either row to row, or coloumb to coloumb.  
> I need to know how to average cells (& ignore zeros or blank cells) that is a 
> mix of connected cell and others that are not connected.....like;
> B5    C5     D5
> B10  C10   D10  
> this format continues for several more rows.
> 
> Not sure if I'll find this help screen again.  If  you don't mined, would 
> you also email the results to:   cusjan@yahoo.com        Thanks.
0
10/19/2008 3:01:01 PM
>First point - blank cells are treated are ignored by the
>average function, as are all non-numeric cells.
>=AVERAGE((B5:D5<>0)*B5:D5,(B10:D10<>0)*B10:D10)

That will include both numeric 0 and empty cells.


-- 
Biff
Microsoft Excel MVP


"ShaneDevenshire" <ShaneDevenshire@discussions.microsoft.com> wrote in 
message news:F346E15E-65FB-46EF-8F29-4CF6677B834D@microsoft.com...
> Hi,
>
> First point - blank cells are treated are ignored by the average function,
> as are all non-numeric cells.
>
> Here is one formula:
> =AVERAGE((B5:D5<>0)*B5:D5,(B10:D10<>0)*B10:D10)
> It needs to be array entered - Press Shift+Ctrl+Enter rather than Enter.
>
> Now if the rows between B5:D5 and B10:D10 are blank, or contain 
> non-numeric
> data you can use:
> =AVERAGE(B5:D10)
> No array entry needed.
>
> If this helps, please click the Yes button.
> -- 
> Thanks,
> Shane Devenshire
>
>
> "Gary" wrote:
>
>> Iam somewhat new to Excell 2003.  I've just learned how to average cells 
>> (&
>> ignore zeros) that are connected, either row to row, or coloumb to 
>> coloumb.
>> I need to know how to average cells (& ignore zeros or blank cells) that 
>> is a
>> mix of connected cell and others that are not connected.....like;
>> B5    C5     D5
>> B10  C10   D10
>> this format continues for several more rows.
>>
>> Not sure if I'll find this help screen again.  If  you don't mined, would
>> you also email the results to:   cusjan@yahoo.com        Thanks. 


0
biffinpitt (3171)
10/19/2008 4:21:41 PM
Reply:

Similar Artilces:

Delete row with empty cell in Column A?
I have a .CSV file exported from our parts control application that needs a bit of tweeking to make it presentable. I would like to delete any row that has an empty cell in Column A. How can I do this? Does it require a macro? STeveK Doesn't require a macro. Select column A. Choose Edit/Go To.../Special. Select Blanks. Choose Edit/Delete... and choose Entire Row from the dialog that opens. In article <epn13akPGHA.648@TK2MSFTNGP14.phx.gbl>, "SteveK" <sk-reeemovethis@dolby-reeemovethis.com> wrote: > I have a .CSV file exported from our parts control ap...

Exchange error
Hello, for one week now, my exchange 2000 server crashes nearly every night at random time, the only solution to have it all restart is to restart the entire server. In the application log, multiple error events are reported like these : Event Type: Error Event Source: Userenv Event Category: None Event ID: 1000 Date: 26/07/2005 Time: 01:27:39 User: NT AUTHORITY\SYSTEM Computer: BELLINI Description: Windows cannot establish a connection to *domain name* with (1364). Event Type: Error Event Source: Userenv Event Category: None Event ID: 1000 Date: 26/07/2005 Time: 01:37:41 User: ...

Sheet protection with unlocked cells
I just upgraded from XP to 2003. I have an XP spreadsheet that is protected except for a number of cells that are open for anyone to enter data in. They use a macro to clean all prior data before they beginn their entery. 1) Why does the macro (when run on my 2003 software) lock all cells? 2) How do I remove data without changing ANY cell properties? Thank you. Hi you may post the existing macro -- Regards Frank Kabel Frankfurt, Germany Norm wrote: > I just upgraded from XP to 2003. I have an XP spreadsheet > that is protected except for a number of cells that are >...

ScrollIntoView Non-Selected Cell ..?
How do i.. Without selecting it.. ScrollIntoView a Cell.. Say... R120C140 ...? ActiveWindow.ScrollintoView .. but how to find the Document Coords in Pixels of that Cell ..? when Rows/Columns are uneven sized ..? Any Feedback would be Appreciated.. Andrew ;-) sub dk() ActiveWindow.ScrollRow = 120 ActiveWindow.ScrollColumn = 140 End Sub This would put row 120, column 140 in the upper left corner of the screen. "Andrew" <NoToSpam@ReadItYourSelf.com> wrote in message news:BeydnRXlerMJfhLWnZ2dnUVZ8gOdnZ2d@bt.com... > How do i.. >...

Referencing a Range of Cells
Hi all, I have named a range of cells on one worksheet, say I called (A1:B10) as "My_Range" on "WorkSheet_1". And then I got another worksheet which want to copy the same value on another worksheet "WorkSheet_2" using the name as a reference. How could I do this? Also, could I specify a particular value inside the named range? For example how could I display the B3 cell in "My_Range"? Thanks for your help. Derek C hi. not sure why you would want to do it this way but try this Sub macSetRange() Dim rng As Range 'range to copy Dim rng1 As...

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Getting Excel Cell Address..
How do I get the cell address of an excel sheet from C#? VBA =Address(4, 5) would return "$E$4" Not able to find an equivalent function when processing the excel sheet from C#. Thanks in advance for all the help. are you trying to formulate formulas into the spreadsheet? whatever function you can access via vba, you can access the same way using office interop regardless vb.net or c# - if you don`t need the formula in the spreadsheet but just need to display the value in spreadsheet or if you are just using your 2 dimensional array to set value into the spreads...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

I can I select cells in a template and link it to a database?
Hi not really sure what you're trying to achieve. Maybe give some more details (and please put them in the body of your message) -- Regards Frank Kabel Frankfurt, Germany "group3" <group3@discussions.microsoft.com> schrieb im Newsbeitrag news:F71B2BF1-8E68-4C54-A528-AA5A53BCFD08@microsoft.com... > ...

VLOOKUP formula displays in cell, will not return data
I had an older version of Excel (97 maybe?) and our office updated to 2007. Now my vlookup will not return data. The vlookup formula appears in the cell or I get the NA#. I've formatted my lookup cells and table to General but still get the same result. After formatting the cell as General, re-enter it (F2/Enter) - if you're still seeing the formula, press Ctrl/~ -- you're looking at the formula layer instead of the value layer. "Chaps" wrote: > I had an older version of Excel (97 maybe?) and our office updated to 2007. > > Now my vloo...

merged cells and creating charts
I have tried to create a chart with data that is in merged cells where I have put in a formula to calculate the average of 4 cells in the row above. When I create the chart, it recognizes all 4 of the merged cells. So the graph has 4 spaces for the data for each number I am trying to plot. I just got 2007 version and do not recall this problem at all in 2003 version Merged cells are to be avoided at all costs. They can be used for column heading but for little else. best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Troubled...

connect to quickbooks
Is there a way or addon to open excel and connect to Quickbooks and then refresh it like I can connect to other database? Jacob ...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

How can I get current cell row number
I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

formula for cell
I have many cells with descriptive text, does anyone know of a formula where I can take for instance a cell with 500 characters and take only the first 30 and paste it in to a new cell. I have used a formula thanks to Gary =left(a1,len(a1)/2 which has worked for other uses I have--now I need to extract only the first 30 characters of a description and I have thousands of cells in my description column..Please Help =left(a1,30) Pete.Cornejo@gmail.com wrote: > > I have many cells with descriptive text, does anyone know of a formula > where I can take for instance a cell with 500...

Saving current worksheet on cell value change
Let's say I want the current worksheet to save when the value changes in cell b15 or b45 or e15, etc. How can I get this done. Thanks. one way: Put this in your worksheet code module (right-click the worksheet tab and choose View Code): Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Intersect(Target, Range("B15,B45,E15")) Is Nothing Then _ ThisWorkbook.Save End Sub In article <807AB41E-E6AB-4762-A5E3-1019F399BDA8@microsoft.com>, scotty <scotty@discussions.microsoft.com> wrote: > Let's say I want ...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

Text lines in a cell
I want to be able to have more than 1 line of text in a cell. -- kboec kboec Wrote: > I want to be able to have more than 1 line of text in a cell. > > > -- > kboec Hi kboec Try this; Enter first line of text, then hold down ALT key and press Enter, no enter second line of tex -- Paul Sheppar ----------------------------------------------------------------------- Paul Sheppard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2478 View this thread: http://www.excelforum.com/showthread.php?threadid=40069 Hi kboec, Type something in the ...

How do I move the data in one cell on a page to another page/cell
What command do I use to move the containts of one cell on a page to a different page and cell??? On the receiving page and cell, type = then navigate to the sheet and cell where you wish to extract the data and press Enter. Assuming you were in cell B1 of Sheet2,and you wanted it to copy the value from Sheet1 cell A10, then Excel would insert the formula =Sheet1!A10 You can just type the formula yourself, but it is probably easier to let Excel do it for you first time. Note, if your sheet name contains spaces, then the formula need single quotes around the sheet name. ='My new s...

How do I change cell that contains a formula to an actual number
I would like to use the results of a formula in a cell as an actual number. Any help would be appreciated? Hi copy the cell and use 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany "Carlton A. Barlow" <CarltonABarlow@discussions.microsoft.com> schrieb im Newsbeitrag news:D8B1F514-E50A-4CB8-98B5-50D6FAA4B739@microsoft.com... > I would like to use the results of a formula in a cell as an actual number. > Any help would be appreciated? An easy way to do this is when editing the formula just type cntl+"=". It immediately chan...

How can I assign a data for one cell from another fixed cell?
I can assign data to one cell by put formular, for example: =H5. But if I add a row, my cell will get data from H6, Excel automatically modified my formular to: =H6 And now I want the data always come from =H5, no matter how many rows I add on top of it. PLease help me. I remember somebody did it, but I forget it. Thank you very much! If I understand your question correctly, you would use absolute references ($ signs to fix the row, column or both)... =$H$5 Rick "new Excel user" <new Excel user@discussions.microsoft.com> wrote in message news:BCAE5E50-1EAF-4ADA-9F24...

How do you convert a formula cell to a constant cell
I use a formula to calculate the value, such as time difference, and want to save the value not the formula. The value (constant) will be used later to adjust both time cells used as arguments in the original formula--thus the need to preserve the value computed rather than the formula. After calculation, copy the cell with the formula then Edit - Paste Special - Values on the same cell. HTH "JQLogan" wrote: > I use a formula to calculate the value, such as time difference, and want to > save the value not the formula. The value (constant) will be used later to > ...

Referring to cells by range names
I used to know a formula years back that allowed me to return a specifi value from a cell by referring to two ranges - one a row and the other column. For example: A B C John 4 6 apple Mary 5 GH 5 Pete 6 v hi After naming the colums A, B and C and the rows John, Mary and Pete, think it was using indirect to refer to A, John, returning 4; or B Pete to return the value v. Anyone know what I am talking about an could they give me that formula? Thanks -- rllane4 ----------------------------------------------------------------------- rllane47's Pro...

Forcing a line feed in a text cell?
Hi. Is there some way of forcing a line feed in an Excel cell For example, I have a spreadsheet containing codes like OL - Wa OL - Ba BL - Ki and I need those three lines to be displayed, in that format, in a single cell regardless of - what size screen the user is using, - what zoom they are using o - whether it is being viewed on a screen or printed hardcopy regard Shan You can insert a line feed by using the alt-enter key combination. --- Message posted from http://www.ExcelForum.com/ Great Thanks ...