EXcluding Zeros from the average in a row

HI
I am trying to average a row of numbers (F35:U35) that have numeric zeros in 
some of the cells. However, I would like to exclude them, and the cells from 
the calculation "=AVERAGE(F35:U35)". Is there a way to do that?

Thanks
-- 
Geo
0
Geo (129)
12/31/2004 1:19:07 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
492 Views

Similar Articles

[PageSpeed] 12

Hi Geo

This array formula will do the job:

=AVERAGE(IF(F35:U35<>0,F35:U35))

To be entered with <Shift><Ctrl><Enter> instead of <Enter>,
also if edited later.

-- 
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse
news:9C2B0B65-1AF8-494C-A48C-F73E24DC782A@microsoft.com...
> HI
> I am trying to average a row of numbers (F35:U35) that have numeric zeros
in
> some of the cells. However, I would like to exclude them, and the cells
from
> the calculation "=AVERAGE(F35:U35)". Is there a way to do that?
>
> Thanks
> -- 
> Geo


0
12/31/2004 2:04:45 PM
In addition to Leo's array formula, you also have the 
option of using:

=SUM(F35:U35)/COUNTIF(F35:U35,"<>0")

so long as your range does not contain any empty cells or 
non-numerical values.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>HI
>I am trying to average a row of numbers (F35:U35) that 
have numeric zeros in 
>some of the cells. However, I would like to exclude them, 
and the cells from 
>the calculation "=AVERAGE(F35:U35)". Is there a way to do 
that?
>
>Thanks
>-- 
>Geo
>.
>
0
jasonjmorin (551)
12/31/2004 2:49:06 PM
Empty cells are not equal to zero.

   =SUM(F35:U35)/(COUNTIF(F35:U35,">0")+COUNTIF(F35:U35,"<0"))

is more bullet proof.

Jerry

Jason Morin wrote:

> In addition to Leo's array formula, you also have the 
> option of using:
> 
> =SUM(F35:U35)/COUNTIF(F35:U35,"<>0")
> 
> so long as your range does not contain any empty cells or 
> non-numerical values.
> 
> HTH
> Jason
> Atlanta, GA
> 
> 
>>-----Original Message-----
>>HI
>>I am trying to average a row of numbers (F35:U35) that 
>>
> have numeric zeros in 
> 
>>some of the cells. However, I would like to exclude them, 
>>
> and the cells from 
> 
>>the calculation "=AVERAGE(F35:U35)". Is there a way to do 
>>
> that?
> 
>>Thanks
>>-- 
>>Geo

0
post_a_reply (1395)
12/31/2004 3:14:55 PM
I know empty cells are not equal to zero. That's why I 
stated this formula is limited to non-numerical values and 
and no empty cells. Your formula is definitely more bullet 
proof in that regard.

Jason

>-----Original Message-----
>Empty cells are not equal to zero.
>
>   =SUM(F35:U35)/(COUNTIF(F35:U35,">0")+COUNTIF
(F35:U35,"<0"))
>
>is more bullet proof.
>
>Jerry
>
>Jason Morin wrote:
>
>> In addition to Leo's array formula, you also have the 
>> option of using:
>> 
>> =SUM(F35:U35)/COUNTIF(F35:U35,"<>0")
>> 
>> so long as your range does not contain any empty cells 
or 
>> non-numerical values.
>> 
>> HTH
>> Jason
>> Atlanta, GA
>> 
>> 
>>>-----Original Message-----
>>>HI
>>>I am trying to average a row of numbers (F35:U35) that 
>>>
>> have numeric zeros in 
>> 
>>>some of the cells. However, I would like to exclude 
them, 
>>>
>> and the cells from 
>> 
>>>the calculation "=AVERAGE(F35:U35)". Is there a way to 
do 
>>>
>> that?
>> 
>>>Thanks
>>>-- 
>>>Geo
>
>.
>
0
jasonjmorin (551)
12/31/2004 4:07:56 PM
Reply:

Similar Artilces:

VB code for checking if a field is not null (for locking a row) is needed
hi, I need to automatically lock a field after it's been written into and has lost focus. Right now I'm using a checkbox, but I want to lock automatically once the field is dirtied (written into) and it loses focus. Actually I need to lock a row, but a field is a good start--I can replicate the code for each field in a row I guess. Any ideas? RL What I'm using now; it works, but you manually have to check a checkbox, and I want to automate this: If boolcheckbox = True Then field001.Locked = True Else field001.Locked = False End If On Dec 24, 8...

Creating a drop down like menu for row of cells?
I have a bit of a problem with an excel worksheet that I have going, th whole sheet is about 450 rows with various bits of input data, an sub-totals for various sections calculated throughout it. In order to reduce the size and make it a little bit easier to navigat through, I am wondering if there is anyway to make rows drop down, lik say with an arrow to the side that a user can click on to have a grou of cells drop down, that way I can only have sub totals and su headings showing. Is this possible to do and how? I've tired searching through help but can't find anything, and don&#...

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a...

allowing rows to break across pages
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel how can i get a row to break across two pages? i can do it in word, and i knew how to do it in excel 04, but i can't figure out how to get it to work in excel 08. what i'm trying to do is the equivalent of the &quot;allow rows to break across pages&quot; function for tables in word (table&amp;gt;table properties&amp;gt;row). there is no similar function in excel. essentially, what i am trying to do is prevent excel from moving a row to a new page once it reaches a certain length. in other wo...

delete rows that have a duplicate id keeping the upper row
I neeed a macro that will delte a row based on a duplicate value in column A; Row col A col B col C Claim Number DCC Warranty Code 1 0132482B DTYC 03D 2 0137448A 1 3 0141614A 01D 4 0141614A 5 0141614A 6 0143504B 1 7 0154120A DW77 01D 8 0154120A 9 0159953A DWL0 03D...

Datedif exclude weekends
Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe...

Timestamp a cell when row is updated.
I am looking for a solution. My spreadsheet has 5 columns that i do not want this to affect. The 6th column is "Last Updated". When i write comments in any cell after H i want it to put the updated date and time into "Last Updated" in that row. Another addition to this would be to check the last updated date, if it is upto 3days from today then turn green, upto 5days yellow and more than 7days red! I hope someone can help! Hi, generally this is very easy and the code is below but your question isn't clear. Is the 'Last updated' colu...

Is it possible to record a number with a leading zero in Excel?
I have a number of product codes that have been created with a leading zero eg 0101 . When they are imported to Excel, Excel removes the leading zero. Is there a way that I can set Excel up so that it does NOT remove these (kind of important) digits? I much appreciate your assistance in advance! Jonathan, Format the cells as Text. HTH, Nikos "Jonathan HB" <Jonathan HB@discussions.microsoft.com> wrote in message news:DE5EFA1C-F2C5-40C4-B12C-CA3598FF3031@microsoft.com... > I have a number of product codes that have been created with a leading zero > eg 0101 . When th...

Format options when format is selected from the top row
...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

Excel
I have data in multiple colums that has been subtotalled by the sum function. I now wnt to copy and paste or export only the rows containing the subtotals to a new sheet. When I do this I get all the rows! It does work if I use the CNTRL key to mark all the individual cells and then paste values only but I find this a dreadful solution. Surely there is an easier way? One way: click the 2 in the outline box to hide the data rows Highlight the range of subtotals F5>Special>click on visible cells only Copy>Paste Special>Values HTH "Russ" wrote: > I have data in mul...

Selecting row when plus is clicked
I have a form/subform that shows up as a grid of master records with plusses next to them, and when the plusses are clicked that part of the grid expands showing a subgrid with detail record. What I would like is for the selection pointer to move to that record when the plus expander is clicked. Is this possible? ...

avoiding plotting zero values on graphs
How do I when plotting points of a graph avoid plotting the zero values. During the start of a new period, there will be several zero values, how can i aviod excel from plotting these zero values "Lee" <Lee@discussions.microsoft.com> wrote in message news:9D957426-691B-49B3-A846-509B09EF8BFF@microsoft.com > How do I when plotting points of a graph avoid plotting the zero > values. > > During the start of a new period, there will be several zero values, > how can i aviod excel from plotting these zero values > Hi Lee, I suggest that, unless the result you ...

Pivot Table Zero Value
Hi, I've seen many questions on this subject, but none of the solutions seem to work for me. I have a pivot table that summarizes billing amounts for 20-25 different data items. Also, the rows have two fields. For Example: Name I.D. Data Total John Smith 7756 U.S. Tax Return $800 John Smith 7756 State Tax Return $250 John Smith 7756 Add'l State Return $0 John Doe 8876 U.S. Tax Return ...

Row Height
Hi All, Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John This macro will scan down column C and if the cell is totally empty it will adjust the height to the default height 12.75, else row height will be 15. Sub Ro...

Excluding zeros
I have stock data in excel which I'd like to chart using the high low close option. Problem is that the data contains zeros on some days. Can I get excel to ignore the zeros instead of plotting them. If not what's the best way of converting these zeros to blank cells so that Excel does not chart them. Thank you Hi Ben, Two ways: 1) replace zeros (or missing data) by =NA() 2) Tools/Options/Chart and specify what is to be done with missing values. Bernard "Ben" <anonymous@discussions.microsoft.com> wrote in message news:075901c3bff5$94a57530$a001280a@phx.gbl... &...

How do you automatically put spaces between rows in a lengthly w.
I have a lengthly work sheet and I want to put spaces between every row--how do I do that? Automatically usually involves VBA coding. Do you really need the inserted rows? Perhaps you could just change the height of the rows to look double-spaced? Here is a macro to do the row inserting. Sub InsertALTrows() 'David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Integer For i = Selection(Selection.Count).Row To Selection(1).Row + 1 Step -1 Rows(i).EntireRow.Inse...

Lost Some Row Numbering
Somehow, when I cloned a worksheet to unused worksheet in my workbook, the numbering (in the left-most column) has lost the 1st 5 values (I failed to copy the 1st 5 rows). IOW, my worksheet starts with Row 6 and continues down from there. I can't find anything in Google that tells me how to fix this, so I'm asking here. Please advise. TIA hi, these lines are probably masked -- isabelle Le 2012-01-19 17:22, Mike Copeland a �crit : > Somehow, when I cloned a worksheet to unused worksheet in my > workbook, the numbering (in the left-most column) has lost the ...

How to select full row of List Control when focus is on it.
I am using List Control.How do i select the first sel(Full row) default,when the focus is on the List Control.The sel is selected when we click through the mouse. Please help and guide me Regards Atul. "atul" <a_fund@yahoo.com> skrev i en meddelelse news:cc80ab6.0401270518.4f0748bd@posting.google.com... > I am using List Control.How do i select the first sel(Full row) > default,when the focus is on the List Control.The sel is selected when > we click through the mouse. > Please help and guide me > Regards > Atul. Something along the lines of m_ListCtrl.SetE...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

Is there any *quick* way to view data records *vertically* instead of as horizontal rows?
Ms Access2003 / WindowsXP Hi Is there any quick way to view data with all fields of a record arranged vertically (i.e. on top of each other) instead of horizontally? Obviously I could copy and paste into msExcell and transpose the whole damned thing. However this wont work for really large tables... Ship Shiperton Henethe "ship" <shiphen@gmail.com> wrote in message news:1175857202.603362.12360@n76g2000hsh.googlegroups.com... > > Ms Access2003 / WindowsXP > > > Hi > > Is there any quick way to view data with all fields of a record > arranged v...

How do I remove numerous blank rows from Excel spreadsheet?
Data is imported from an external source and contains numerous blank rows within the data. How can I remove the blank rows without having to select each one? There are sometimes hundreds of them. Thanks! one way highlight the range press F5>click special...>click the radio button "Blanks">OK Now press ctrl+- and delete rows "JP6262AMY" wrote: > Data is imported from an external source and contains numerous blank rows > within the data. How can I remove the blank rows without having to select > each one? There are sometimes hundreds of them. T...

leading zeros as a cell format
Hi All, I have an issue with formating cells. I have a permit number that has leading zeros sometimes, so I must display them when entered. I have tried 0?-??????? and 0#-####### but no luck. Heres is what my permit number look 01-2345678 & 02-8765432. thanks, Dave Format as text. "Dave" <davidstevans@gmail.com> wrote in message news:4c0dd9d3-5785-44cd-9d1b-a5f0d2280e28@u16g2000pru.googlegroups.com... > Hi All, > > I have an issue with formating cells. I have a permit number that has > leading zeros sometimes, so I must display them when entered. I have &...

conditional formatting
I'm not understanding conditional formatting re dates. I have an Excel sheet which has a "Received Date" column. After three days from the date entered in the "received date" column, I would like the text for the text for the row to turn orange, after five days I would like the text for the row to turn red +bold. After 7 days I would like for the rows in red +bold to show turn yellow and a message box, on document start-up, to show the user that these rows have overdue tasks that need attention *now* (basically an irritating nag box) My first concern is to get the co...

excluding repeating values
I have a column wuth date and some entries are repeating. I want to copy that data to another column, but do not want to copy duplicate values ( I want to exclude repeating values when copying and pasting). Is there an option to do that? Thanks. Neda Take a look at http://contextures.com/xladvfilter01.html#FilterUR In article <CE251167-21F0-470D-9641-508FD96D54FD@microsoft.com>, "neda5" <neda5@discussions.microsoft.com> wrote: > I have a column wuth date and some entries are repeating. I want to copy that > data to another column, but do not want to c...