IF and AVERAGE

Hi,

I have a column of numbers, some of which are zero. 

I want to obtain the average of the numbers in the column and exclude the 
zeros from the calculation.

Thanks!
0
James6891 (359)
1/5/2006 3:14:02 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
567 Views

Similar Articles

[PageSpeed] 15

Hi James

Assuming your range is A1:A50
the formula would be
=AVERAGE(IF(A1:A50<>0,A1:A50))
entered using CONTROL & SHIFT & ENTER

Hope this helps

-- 
Cheers
JulieD

julied_ng  at hctsReMoVeThIs dot net dot au


"James" wrote:

> Hi,
> 
> I have a column of numbers, some of which are zero. 
> 
> I want to obtain the average of the numbers in the column and exclude the 
> zeros from the calculation.
> 
> Thanks!
0
julied (23)
1/5/2006 3:22:02 AM
On Wed, 4 Jan 2006 19:14:02 -0800, "James" <James@discussions.microsoft.com>
wrote:

>Hi,
>
>I have a column of numbers, some of which are zero. 
>
>I want to obtain the average of the numbers in the column and exclude the 
>zeros from the calculation.
>
>Thanks!

=SUM(rng)/COUNTIF(rng,"<>0")

where rng is your reference to the column of numbers.




--ron
0
ronrosenfeld (3122)
1/5/2006 3:49:16 AM
Reply:

Similar Artilces:

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 ...

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 ...

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...

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...

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)] / (...

Average Function
Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

averages
Hi All Trying to work out the best way to get an average answer based on a set of answers The answer will be 1 - 10 across a number of questions e.g B C D E F Answer Q1 Q2 Q3 Q4 1 1 1 3 3 2 1 0 1 2 3 0 1 3 3 4 1 0 1 1 5 1 1 6 7 6 2 2 5 2 7 2 3 3 4 8 2 1 7 6 9 1 2 1 3 10 2 2 8 7 Total 13 13 38 38 Average ? ? ? ? What is the best way to get the average response to each question. i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure this is right....

Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly. I have a spreadhseet: Date Data 2/1/99 1.15 9/1/99 2.42 16/1/99 1.24 through to..... 25/2/05 3.54 How can i calculate the average of every month between 1999 and 2005? In other words, i need something which looks up all the weeks in a particular month (ie Jan 2001) and averages them out into a table: 1999 2000 ......... 2005 Jan 2.45 6.42 Feb 3.64 etc etc. I was thinking vlookup, but don't know how to average in a vlookup. I would ...

Moving Average
Is there a formula for calculating the moving average for a group of numbers ? Thanks I'm assuming that you want a running average (at least, that's how I am interpreting it) Assuming your values are in column A and your average in column B the formula in B1 should be as follows: =Average($A$1:A1) then just copy and paste it down the rest of the column and it will calculate the average as numbers are added. Steven Bitaxi sbitaxi@yorku.ca wrote: > I'm assuming that you want a running average (at least, that's how I am > interpreting it) I think the OP want...

Averaging?
This must be easy to do but I cannot figure it out... I have 150 rows of data with information in cell A, B, C and D. Cell A is simply a description for each row and cells B, C, and D contain numeric values between 1 and 100. I need to average each row to its respective cell E and then sort row A from highest to lowest based on the average value in row E. Example >> A B C D E Song1 52 45 25 Song2 33 55 88 Song3 44 94 22 Anyone!?!? TIA!! David@Rocketmail.com One way: E1: =AVERAGE(B1:D1) Copy down to E150. Select E1. Choos...

Running Average or YTD average
Is there a simple formula for calculating a running average? I can do accomplish what I want to do but it takes 4 different steps or functions to get there. Example of what I would like: A-1 thru A-52 equal weeks of the year. We'll say that A-5 represents the 5th week of the year. Income for these weeks will be entered in it's respective cell as it comes due. In the cells that represent the weeks that have not yet come, a "0" is in them. I want to be able to have one cell that reflects the year todate average of income, and not be affected by the zeros. -- lsmft -------...

How to obtain Averages from a list of multiple items?
Example:Excel worksheet-Column A has list of Cat or Dog (say 15 items)-Column C has their age in days old (10,3,4,etc) I need (2) Averages- Average age of Cats and Average Age of Dogs Hi! =SUMIF(A1:A50,"cat",C1:C50)/COUNTIF(A1:A50,"cat") Do the same for dog. Biff "MadameJunk" <MadameJunk@discussions.microsoft.com> wrote in message news:B0E6C651-E09D-4FA4-A0BB-F859A88CA81A@microsoft.com... > Example:Excel worksheet-Column A has list of Cat or Dog (say 15 > items)-Column > C has their age in days old (10,3,4,etc) > I need (2) Averages- Aver...

Dynamic annual average
Hi, I keep a rough weekly diary on the development of a certain numeric value. Now I've got a series of data entries in a chart as follows: A1 - date of entry, eg. "23rd July 2006" A2 - reading data value, eg "100" I want to create a dynamic average on line A3 which tells me the average of data entries during the past year. Problem is, I've read the data value in irregular intervals and not every day (last year for example on 22nd July, the year before 10th July etc). My Excel understands the dates entered and draws a neat proportioned chart on development of data...

Average Sale #2
I want to see if a recent spend �15 and receive free box of chocolates made a difference to the average sale. How can I see the average sale by day on a report? Chris the simplest way to do this is to point Excel at your database and drag the information out - use the built in Excel features to summarize your sales by date - The TRANSACTION table is the one to use. "chris allsopp" wrote: > I want to see if a recent spend £15 and receive free box of chocolates made > a difference to the average sale. > > How can I see the average sale by day on a report? ...

averaging class grades
All I could get it to do was average 3 weeks grades instead of the 6 weeks I put in for (from G1:M1) and by highlighting the whole area-I used the =AVERAGE,etc. I'm not sure what I did wrong still. Please stay with one thread. Let's say you have the data like this John 6 8 9 5 6 7 Mary 6 8 3 7 8 9 .... Zak 5 6 7 8 4 7 .... And that John's name is in A2 and his marks are in B2:G7 and Zak's in B22:G22 The average of John's marks is found with =AVERAGE(B2:G7) The average of week 1 marks with =AVERAGE(B2:B22) best wishes -- Bernard V Liengme Microsoft Excel MVP htt...

AVERAGE issue
Hi, I'm averaging 52 rows which are week totals. I have a result column for the average. Problem: If I have only say 30 of the 52 weeks currently to average, the formula that does my week totals has a zero in the week rows that haven't happened yet, so my running average is way too low due to the zeros. Is there a way to include an if condition for the averaging, like include this row IF the value is greater than zero? Or, is there some simpler way to solve this? I could of course not include the totals formulas until that week has data, which would give me blank cells for unu...

Averaging in arrays
Hi, I thought I saw the answer to this before, but I can't seemt to find it Here's the scenario Column A has lists of Cities (ie: Boston, Chicago etc..) Column B has List of inventory Column C has amout of time in hours:Min:sec sitting in inventory Column lengths could be in hundreds I want to do formula that Produce results for example Boston Blue widgets 236:45:00 All help appreciated Without testing... =AVERAGE(IF((A1:A1000="Boston")*(B1:B1000="Blue Widget"),C1:C1000)) Array-entered, meaning press ctrl/shift/enter. HTH Jason Atlanta, GA >-----Ori...

Why can't you average an average ?
What's the formal or definitive explanation for why you can't average dataset of averages ? I understand about the skew effect if no account is taken of th original sample sizes for the averages to be averaged, but what if the are all the same size ? Would it then be valid to average them ? Cheers Blewy -- Blewy ----------------------------------------------------------------------- Blewyn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=279 View this thread: http://www.excelforum.com/showthread.php?threadid=27751 You can. Example: The average of 2...

2 part question on averaging
Hi, I have the formula =SUM(G34:V34)/COUNTIF(G34:V34,"<>0") Which does a great job averaging the data as long as there is a numeric value in one of the cells. However, if there are no numbers I get a div/o error. Is there a fix to that? Question 2; Is there a way to average say the 5 highest values in the G34:V34 row? -- Geo =IF(SUM(G34:V34)=0,"",SUM(G34:V34)/COUNTIF(G34:V34,"<>0")) and =AVERAGE(LARGE(G34:V34,{1,2,3,4,5})) -- HTH ------- Bob Phillips "Geo" <Geo@discussions.microsoft.com> wrote in message news:B2C406...

Average
Hi, I need the average calculate, but all the zero cell I need delete, to correct calculate. It exists some formula if excluding the zero cells ? Sorry, I�m brazilian and my english is very bad ! Best regards, Luis Alcantara =AVERAGE(IF(A1:A10<>0,A1:A10)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Luis Felipe Alcantara" <lfvasconcelos@hotmail.com> wrote in message news:%23BIfapjVEHA.1152@TK2MSFTNGP09.phx.gbl... > Hi, > > I need the av...

Compound Average Growth Rate
Does anyone have any ideas about a formula for CUMULATIVE Compound Average Growth Rate (CAGR) for budgeting purposes??? --- Message posted from http://www.ExcelForum.com/ Do we assume you have a bunch of individual line items, which have a growth rate, for which you are calculating CAGR using the RATE function? Then you want to sum the line items, and calculate the CAGR for the total line? If so, calculate CAGR on the total line the same way you do it for the line items -- feed RATE your term (likely the same as the line item), PV (sum of line item PVs) and FV (sum of line item FVs). It ...

Weighed Average of a weiged average when there are blanks
This might be a bit complicated, at least for me it is I need to calculate a total weighed average of a few weighed averages of grades so i have few columns of credits like: cr. column1 3 2 5 4 4.5 cr. column2 2 4 3 3 2 cr. column3 2 2 3 3 5 then I have columns of grades between 0 and 5 grd. column1 2 2 (blank) 4 5 grd. column2 1 1 0 (blank) (blank) grd. column3 (blank) (blank) 4 4 4 in some course I havent gotten grades yet so there are blanks I can get the weighed average of corresponding columns with this kind of formula: =SUMPRODUCT(C1:C5;D1:D5)/SUMIF(D1:D5;">0";C1:C...

Time Averages/ Time of Day and Minutes
I am working on a database that tracks dispatch time/ arrival time/ and how many minutes to scene. How do I 1: record the time and then query the time to show an average dispatch time? (I assume the arrival time equation will be the same) 2: set up a query to calculate the difference in arrival time from dispatch time, and then the subsequent average response time? On Mon, 28 Jan 2008 16:04:02 -0800, Scotty <Scotty@discussions.microsoft.com> wrote: >I am working on a database that tracks dispatch time/ arrival time/ and how >many minutes to scene. How do I 1: record the tim...

average
i have a summary of sales statement like jan 5 ,feb 5 ,mar 5 , apr 5, jun 5 , jul 5, aug 5 , sep 5, oct 5 , nov 5 , dec 5 and after that we do total for all 12 months 60 so now we find average like this =average(.........) all numbers but if we dont have a sell in jan month n we dont have a sell in feb month so remaining 10 months so shown me in total only 10 months sale not 12 months sale autometically we generate only once time formula either we have a sell in jan month or either we have a sell in feb months i also try this formula total/count(...........) or if formula if(ref>0,r...

averaging
Hi If I want to obtain the average of values in a column, using only those cells which have a value in them, which function do I use please? thanks A Use =average() it ignores cells that are empty and cells that contain text. Alex wrote: > > Hi > If I want to obtain the average of values in a column, using only those > cells which have a value in them, which function do I use please? > > thanks > A -- Dave Peterson ...