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
1515 Views

Similar Articles

[PageSpeed] 39

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:

choosing first instance of non zero data
how do i tell excel to count the number of cells until it reaches 3 zeros? Zip w.1 w.2 w.3 w.4 w.5 w.6 w.7 w.8 80001 0 3 2 0 1 0 0 0 start here | | End here Sheet 2: (output) Zip Count 80001 4 ...

Sorting rows #2
I have a .csv spreadsheet that contains the following details in the columns A,B and C -2.07762 53.5114 GATSO:6354@30 -2.74783 53.3244 GATSO:3746@60 -0.76298 51.98999 GATSO:5244@30 -0.24594 52.58497 GATSO:0890@30 -2.77217 55.74874 GATSO:3110@60 -0.32181 51.33441 GATSO:4834@40 -0.39686 52.57074 GATSO:5951@70 There are over 3000 rows in my spreadsheet. What I would like is to sort the rows according to the data in the final column. So, I would like ...

count number of rows in 2 worksheets
HI, Anyone can help? I need a macro code to count number of used rows in two seperate worksheets and compare. If the number do not match error message shall appear "Sheet1 has (blank) number and Sheet2 has (blank) number". (blank) being the number of used rows per sheet. Can this be done? thanks! You have some answers in your first posting of this question. However, I'd like to tell you that your question is not defined very well. You have to tell us what you mean by "used rows". For example, if there are blank rows inside your data, are they to be coun...

Move Multi-rows of numbers to one column
Hello, I keep thinking there must be a way to do this and I haven't found it yet. I have a spreadsheet with 8 columns of about 15 numbers each so it looks like this (example): 5079 5080 5082 5091 5108 5109 5111 5115 5117 5118 5119 5120, etc. I really want all of these numbers in 1 column so I can use sort to make me a list of all the numbers. How do I do this without cutting and pasting all of the numbers from the second column below the first, and then the third below the first and second, etc. Thanks, JW Jerry wrote: > Hello, > I keep thinking there must be a way ...

Trendline with moving average
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When using a trendline with a moving average, what type of windowing is used. A square window is simple but generates harmonics that are artifacts; whereas, a raised cosine or Hamming window does not have this problem. <br><br>Does any one know what type of windowing is used for a trendline with a moving average? Tested the window by using the equivalent of an impulse function. The windowing is a square window. Has a number of issues the most significant of which are the harmonic artifacts inversely pro...

Extend Rows and related formula downwards using functions not macros
I have a simple worksheet to amortise a loan. I would like to use a formula to extend the rows downwards to the number of payments (Number of years x Number of payments per year) using a formula. For example, if there were 26 payments per year over 1 year, there should be 26 rows. Now if I change the number of years to 2 years, two things should happen: 1. The number of rows should expand to 52 from 26 2. The sum of the interest paid should include these extra rows.i.e =Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to rownumber 26). Currently I am using a simple formula alon...

e-mail indvl rows to e-mail address w/in row
This is a longshot.... Any ideas on how to send individual rows of data, in separat documents, through Lotus R.5, to the e-mail addresses entered in Colum A of each row, preferably complete with the column headings -- Message posted from http://www.ExcelForum.com The following *may* give you a clue: http://www.j-walk.com/ss/excel/tips/tip86.htm Dunca -- Message posted from http://www.ExcelForum.com Hi see: http://www.rondebruin.nl/sendmail.htm -- Regards Frank Kabel Frankfurt, Germany "upstate_steve >" <<upstate_steve.16yz4c@excelforum-nospam.com> schrieb im Ne...

How to copy rows from another spreadsh without copying of hidden r
How to copy rows from another spreadsheet without copying of hidden rows? Ruslan, Hit CRTL + A to select all cells. Or select your range Edit, Go To, Special, Visible cells only, OK Copy and paste to new sheet. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 2003 ** remove news from my email address to reply by email ** "Ruslan" <Ruslan@discussions.microsoft.com> wrote in message news:13230937-C092-4F15-BD31-32AA067B9404...

Automatically move row from one tab to another..
He there, I have a question. I'm working on a salessheet. The first tab is a summary of al offers I make to the customers. For every offer I have the option of giving them a status: Pending, acquired and bounced. When I send an offer to a customer the order gets the status pending. What I want to do is the following: When an order is acquired or bounced I change the status accordingly. What I want is that as soon as I change the status the entire orderrow is placed inside a tab named acquired or bounced. I don't want to copy paste everytime an order is "closed" Somebody ...

Automatically not print rows in Excel with a zero value?
I use Excel for billing invoices. Have over 100 items. Never use all items. I manually delete each row so invoice is not 3 pages long. Don't want to show all these no value rows. Would like to have Excel only print the rows that contain a value--so I don't have to eliminate these rows every time I create a new invoice. Hi DJP just an alternative approach, why don't you design the invoice for the "average" number of items you have (and so it looks pretty on 1 page) and then add rows in if you need extra ones? i have an example invoice workbook with code that does thi...

Macro to remove contents of cell and move all other contents up one row
I've very lillte or no experience of macros and got some very usefu help from a kind user of this forum a couple of days back and I'm bac looking for more help. Its a bit long winded but here goes. I need a macro which checks the contents of a couple of ranges o cells, starting with the first range of cells lets say it checks cell A4,B4,C4,D4 down to cells A20,B20,C20,D20. Now if for example cell A7,B7,C7 and D7 have values (note the formulas used in these cell means that if A7 has a value then B7,C7 and D7 will also have a valu if A7 is blank then B7,C7, and D7 will be blank) and no...

Pivot Table suppresses/hides zero values
I have a list of retail chains... products and amounts sold. My pivot table does not show any of the retail chains that don't have sales. I've added zero data in the Access table I'm pulling from, but that doesn't seem to help. My Pivot table options and my general option are NOT set to hide zero values. It's something the table seems to be doing automatically. Anyone have a suggestion? Check the field settings (double click the grey field names) to mak sure "show items with no data" is checked -- Message posted from http://www.ExcelForum.com ...

i gt zero's when i open my last excel document, why?
help, i got office 2003, i use excel, whys my doc;s filled with noughts? ....sounds like you have a lot of formulas that are linked to another file. The other file will need to be opened as well. "larrydesires" wrote: > help, i got office 2003, i use excel, whys my doc;s filled with noughts? ...

I want to delete duplicate rows of similar info and use the lates.
I am trying to paste new data into a workbook that may already contain the it. I need to filter out the old data and insert or keep the new. The new data is not in the same order as the old. Is there a filter or something that will find a duplicate and give me the option of which row I want to delete? Add the new data to the old data, and go to data/filter/advanced filter and select "unique records only". This will show you unique records of both the old and new data. Then copy these unique records into a new file. Hope this helped. >-----Original Message----- >I am try...

CSV file dropping lead zeros
I have a CSV/Txt file that I am opening and I tell it that it is tab delimited. I find that my first columns, that have numbers in them, are dropping the leading 0's. I don't want that. I need the leading zeros. How do I tell excel not to do this? Thanks, Tom Use NotePad to look at your .csv file. I bet the leading 0's are lost when you re-import into excel. If you have to reimport into excel, you can rename the .csv file to .txt. Then when you open the text file, you'll see the data|text to columns wizard. You can specify the format of each field--including Tex...

Print w/ column & row labels
Is there a way to print the column and row labels (not headers I set myself, but the identifiers that are there already)? John, File - Page Setup - Sheet tab - Row and column headings. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "John Stewart" <anonymous@discussions.microsoft.com> wrote in message news:202101c47ca8$3c50ba50$a601280a@phx.gbl... > Is there a way to print the column and row labels (not > headers I set myself, but the identifiers that are there > already)? GREAT! Thanks. >-----Origi...

Cannot Delete rows in AsyncOperationBase table (MSCRM DB is 73GB on SBS)
In my ignorance, I let the MSCRM database grow to a huge size. I became alarmed at 58GB and 2 days later it was 73GB. With the help of my solutions partner, we found out that we have to manage the size of my AsyncOperationBase table by deleting old completed rows. The scripts always timeout. Then I decided to try and delete just one row using SQL Management Studio. Here is the error. No rows were deleted. A problem occurred attempting to delete row 1. Error Source: .Net SqlClient Data Provider. Error Message: Timeout expired. The timeout period elapsed prior to completion of the operati...

Printing header row on each page
I know I've done this a million times, but I'm suddenly braindead. How can I print the header row on each page? Thank you Phyllis Go to File/Page Setup/Sheet and set what you want in 'Rows to repeat at top' Andy. "Phyllis Humrich" <HumrichP@cityofreno.com> wrote in message news:0f6a01c37df7$329697b0$a401280a@phx.gbl... > I know I've done this a million times, but I'm suddenly > braindead. How can I print the header row on each page? > Thank you Are you really talking about "page" or "sheet" ... worksheet that is ?...

How to make repeating rows stop
I have a client with Excel 2002. She has row 7 set to repeat. However after row 210 (which becomes a notes section) she wants it to stop. Is this possible? Thanks, carla bradley carla.bradley@DELETETHIS.lgeenergy.com I don't believe this is possible. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Carla Bradley" <anonymous@discussions.microsoft.com> wrote in message news:01e001c48b9f$de40b4e0$a401280a@phx.gbl... > I have a client with Excel 2002. She has row 7 set to > repeat. However after row 210 (which be...

Grouping rows with the plus/minus icon in the first row
I found part of my answer in another post (see below) with the code: ActiveSheet.Outline.SummaryRow = xlAbove ActiveSheet.Range("A2", "A10").Rows.Group But what if I wanted to Group other rows in the same worksheet in the same fashion. For example, I would like to also apply for range A12:A20 with A11 as the row with the +/- sign. And then to A22:A30 with A21 as the row with the +/- sign. And so on.. Also, I am fairly new to using VB, so what would be the entire set of code to place in the in VB editor? Thanks!! http://www.microsoft.com/office/comm...

delete extra columns and rows
I have an excel worksheet. Currently I'm only using 700+ rows and about 15 colums. However I could scroll down to the 10 millionth row if I wanted. How do I get rid of all those extra rows? Same problem with the cloumns. I basically want someone to open up my file and see that there aren't an infinate number of rows yet to be populated. I found this macro that was supposed to delete all empty rows. it ran and they were still there. Excel ALWAYS has 65536 rows and 256 columns, and you can't change that no matter what you do. However, you can hide them. use F5 (goto). and ...

How do I stop Excel from automatically resizing my row height?
I have a spreadsheet with approximately 3800 rows and about 40 columns of data that I'm constantly filtering and massaging data in. It's getting frustrating to have my rows all of a sudden jump to a height of about 1/4 of the screen when I shrink some column widths to fit more columns on the screen. (Freeze panes won't allow me to split the screen also.) How do I disable the autofit row height? ...

Create a check box for each row
I am trying to create an issues list in excel, and I want to add a check box in the field as the issue is resolved. Hi Nicholas, Install by rightclick on sheettab then view code then insert the following: Private Sub Worksheet_BeforeDoubleClick(ByVal _ Target As Range, Cancel As Boolean) If Target.row = 1 Then Exit Sub 'row 1 s/b column titles If Target.Column <> 2 Then Exit Sub If UCase(Target.Value) = "P" Then Target.Value = "" Else Target.Value = "P" End If End Sub Format the entire column with Symbol Font ...

sum of rows...
i have a number of rows that i want to sum, the problem is that these rows have formula's if there is no real value. is there a way to sum the actual values and ignore the cells that have formula's? for example, A1=5, A2=2, A3=, (formula), A4=1. i get an #N/A. any suggestions? TIA James If A3 housing a formula that returns #N/A, try... =SUMIF(A1:A4,"<>#N/A") instead of: =SUM(A1:A4) "James" <james@guidant.com> wrote in message news:uk1Y%23DmTEHA.3336@TK2MSFTNGP10.phx.gbl... > i have a number of rows that i want to sum, the problem is that th...

Creating a Gant Bar in a row for a task
Howdy! Can anyone tell me how to create a Gant Chart bar effect by shading cells on the same row as a task with a duration and a start and end date? I am a cheapskate and do not want to buy MS Project to do this very simple task, if I don't have to. Plus I don't need all the bells and whistles of MS Project for this. The sheet I have created looks similar to a MS Project Gant Chart view with each row representing a task and each row has the following cells(columns) Task ID, Task Description, Duration, Start date and End date, then I have 52 columns representing weeks. I have a lot ...