Averaging Numbers But Not Including Zero

Sep 11, 2009

I have a row of cells containing numbers, some of which are 0, how can i get an average where it averages all cells except the 0, as at the moment it is distorting my results.

View 3 Replies


ADVERTISEMENT

Excel 2003 :: Averaging Cells In Multiple Sheets And Not Including Null Or Zero Value?

Jun 24, 2012

I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.

I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.

I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!

this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!

and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?

The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.

Using windows 7, excel 2003

View 3 Replies View Related

Averaging A Set Of Numbers

Jun 25, 2009

I need to average out a set of numbers but the amount of numbers inputed will vary, these number can also be either positive numbers or negative numbers but will never be 0.

How do i do this and I would like this in formula terms?

Say cells a3,b3,c3 were the numbers that I want averaged but there is not always a number inputted in each of these cells. I want the answer to show up in cell a4

View 9 Replies View Related

Trick On Averaging Numbers

Mar 21, 2009

I am looking for the correct way to average a group of numbers.

I know the formula but it doeskin work correct.

I am trying to average this numbers below.

Now when I do this I use this formula

=AVERAGE(G1:G17)

and get this result
-33.079

Now the only problem is, with baseball lines +100 and -100 are both the same and there is never and two or one digit lines like
-4
or -88

its always at min -101 or +101

127
163
146
-119
-124
-139
119
-133
-113
-137
-130
-142
-101
-104
114
111
-111

View 12 Replies View Related

Averaging Only The X Highest Numbers In A Row

Jun 7, 2006

Is there a way to average only the X highest number of scores in a row?

What about if there is an additional condition, i.e., if the column
heading says "Quiz", for instance, instead of "Test"?

View 9 Replies View Related

Averaging Smallest, Non-zero Numbers In A Row

Nov 24, 2006

I'm struggling to get the syntax right for this formula. I've been trying variations on the following formula but can't get it right:

=AVERAGE(SMALL(IF(AP8:AZ80,AP8:AZ8),1)))


Columns AP:AZ:

1.8921.7621.6501.1741.2261.2991.3020.0000.0001.1461.333
Any column in this range could have a zero value.

What I need: The average of the 8 lowest non-zero numbers in range AP:AZ

View 9 Replies View Related

Averaging Numbers If They Match A Criteria

Feb 16, 2010

I am running Excel 2003. I have two columns of numbers (column A and column B)
column A has 1s and 0s. column B has latencies (ie. 100-500ms). When column A has a 0, i would like to know the latency of the following row in column B. from this i would like to average all of these latencies that are found. for example.

column A has 1,1,0,1,1.
column B has 100, 200, 300, 400, 500.
so since A3 is 0, then i want the latency of the following row in column B which is B4 or 400. and then average these findings.

View 3 Replies View Related

Averaging Numbers From Similar Names

Oct 14, 2009

I have tried looking for similar questions in the forum and couldn't find them. If you know of any, please let me know. My problem is that in one column I have peoples names that are repeated. I need to average numbers that are in the same row as their name. So for example:

NameDateworkPagesRevisions Revisions/PageRH9/16/09Sandy land39320.82Bryce9/18/09Price mixed use1111030.93Bryan F.9/21/09Letter (rates)6325.33Bryan F.9/21/09Bountiful Nursery64871.36Randy9/22/09SJ Subdivision58220.38RH9/25/09Park City office Condos76881.16Bryce9/25/09Evanston Motel62210.34

this list is continually added upon monthly. So for Bryce I would be able to pull 111 and 62 and have an average of 87.5 from the pages column. But if I were to add another row farther down with his name, it would change the average.

I was thinking that a repeated vlookup would work if it stopped when there was a blank in the cell, but I am unsure on how to do this. If you have any questions, let me know. I was hoping to have it in a formula in a cell above the table.

View 9 Replies View Related

Averaging Middle 10 Numbers Of A 12 Number List

Aug 1, 2014

How I could get an average of a 12 number list while at the same time removing the highest and lowest numbers. They aren't in any order either.

1087.2
1041.35
1040.35
1049.65
800.45
734.15
835.7
1157.15
1145
1098.5
788.6
1265.85

View 6 Replies View Related

Count Numbers Including Text Only

Jul 6, 2006

I need to count the number of entries made in an column provided it should exclude text values but it should count all the values which is combined of text and numbers ..... I tried count but it's only counting cells containing number....

View 7 Replies View Related

SUM For Values Between Two Numbers Including Blank Cells?

Jun 10, 2006

Looks like this is the best place for all my Excel troubles, so...

I want to SUM all the values between two numbers, for example 1 and 6 in 20 cells. For this I have the following array formula:

=SUM((H1:H20>0)*(H1:H20

View 9 Replies View Related

Finding Number Closest To Zero (including Negative Numbers)

Nov 25, 2006

a spreadsheet in Excel. I have names with scores. Then I have the winning score. I need a formula to find the score closest to zero and to display the name of the winner.

Ex: Names A1:A4 and Scores B1:B4. Winning Score in B6 and list name in B7.
Ana 16
Bob 2
Charles 8
David 11

Winning Score 10

Answer should be 11 which is David, since David is only -1 away compared to the others.

View 9 Replies View Related

Averaging

May 27, 2009

How can I do an average in Excel 2003 when it's not a range but I also don't want to count a certain cell when there's no data or zero? I have attached the spreadsheet.

View 6 Replies View Related

Averaging Only If NameID Is The Same

Apr 8, 2007

I'm trying to average data for the past 3 years. My spreadsheet is setup like this.

Year, NameID, Salary, Average Salary

I have the spreadsheet sorted by NameID so most people in the database will have 3 entries right after another. How do I determine an average salary for the person across all 3 years?

I need the average salary to display across from the most recent date if that is possible as well i.e. if the latest salary date is from 2006, I would prefer that is entered into the row corresponding to 2006 and leave the 2004/2005 rows blank.

Also, some people might only have 2 years of data so keep that in mind as well.

View 10 Replies View Related

Duplicates And Averaging

May 20, 2009

Column A has approximately 50,000 rows with unique property parcel numbers. Column B has numbers that represent an elevation point on the parcel. The parcels are not level. The elevation of a parcel varies depending where one measures. So,...I have many rows with the same parcel number in column A but the adjacent column B shows a different elevation. Is there any way to:

1) remove all of the duplicate column A parcel number rows so I have just one column A parcel number row.

2) get the average of all the column B elevations to show up on the same one row?

I should only have approximately 15,000 rows if I could get rid of the duplicates and get the average elevation. I have version 2007 but I have to share with others who have version 2003. I don't know if that makes a difference but I thought I should mention it

View 3 Replies View Related

Grading And Averaging

Jun 10, 2009

I have received a task to do for school and I tried my heart out but I can't seem to figure this out. It's really simple but I can't seem to do it... I'm not very good with excel.

The excel file is attached .....

View 14 Replies View Related

Averaging Words

Dec 1, 2006

I have a spread sheet and it has a column in it with a drop down menu. The words in the menu are Hot, Medium and Cold. I want to have it tell me which one if showing up the most.

View 13 Replies View Related

Grouping And Averaging

Dec 2, 2008

The organisation I wish to report on has many cost centres which each contain many people. These people are on many different grades, and each are on differing salaries (even those in the same grade).

One “reporting group” has many cost centres.

There are several reporting groups.

I need to report on average salary per grade / per reporting group.

I have attached some dummy data. The “rep group” tab displays the reporting groups and the cost centre mappings (ie reporting group England contains cost centres 1, 2, 3, 4, 5, 6 and 7). Note in reality the cost centres are not this simple, they are 6 digits and varying ranges.

In the “salaries” sheet each individual is listed along with their cost centre (in column c) and their grade (column d). Their salary is shown in E.

View 11 Replies View Related

Averaging Time In VBA

Oct 13, 2009

I have a column of data in Excel in the [h].mm.ss format. These values range from a few minutes to hundreds of hours. I need to get the average of these times.

My code is below. I'm getting a type mismatch error on the line that tries to add the time to the total. The line is in red. I've tried using a Integer, Long, Double, and Date as variable types for this particular variable. All with the same result.

View 9 Replies View Related

Averaging Of N Weeks

Apr 28, 2007

I am attempting to find the average units of the last "n" weeks. I wish to input the required number of weeks in cell D20 which will show the average in cell D22. In this particular example, I have manually calculated using the average formula....

View 9 Replies View Related

Formula For Averaging

Jun 16, 2007

I need some help with a formula. I've tried daverage, cant seem to get it to work. below is an example of the spreadsheet.

A B C D E
Date Name Project Score Pass?
5/1 Joe Test 55% No
5/1 Jan Quiz 88% Yes
5/4 Jon Test 100% Yes
5/9 Gary Test 75% Yes
5/11 Joe Quiz 90% Yes
5/18 Mary Test 45% No

Ok, I need a formula that I can execute from a different worksheet than the data is on. I need the formula to give me the average of the scores in column D for the criteria in Column C "Test". I tried daverage=(A1:E7, "Project", C1:C2) I cant get it to work. Do you guys know any other way to do this?

View 9 Replies View Related

Averaging A Vlookup

Jan 27, 2009

in Sheet1, i have 'part number' listed in column A and 'customer number' in column H. in column P, i have 'customer price' (for that part). so there are multiples of the same part numbers listed in column A.

in Sheet2, i have a summary showing totals for the parts (regardless of the customer), which includes SUMIFs for other columns such as pieces sold, etc. what i am looking to do is get the 'customer price' average by part (in column P/worksheet1) for each part on Sheet2.

Sheet1 sample:
Column A / Column H / Column P
31397 / 1001240 / $6.60
31397 / 1020312 / $6.65
31397 / 40020 / $6.63
31832 / 1047493 / $4.22
31832 / 1035195 / $4.22
31832 / 40017 / $4.40

So for Sheet2 i would like it to be like:
Column A / Column B
31397 / $6.6267
31832 / $4.28

i also need this formula to work if column P has any cells with zero entered.

View 9 Replies View Related

Averaging Columns

May 16, 2009

I'm making up a list of average scores across a season. I have an average column and I need to be able to deduct 10% from this average, then have excel ignore any scores below this new number and make up a new average (kinda like a handicap score).

I have columns for average (BD), 10% of this (BE) "ignore any score lower than"(BF) but Im not sure how to get excel to add up only the scores below the number in BF and make a new average column of only these in BG -

Last column would be to give each person a classification based on cutoffs (for instance 1-10=C Class, 11-20=B Class, 21-30=A Class etc etc). Is there a way to make "if BF=xxxx, make BH A, B, C etc?

View 9 Replies View Related

Averaging An Array

Jun 29, 2006

i've got a worksheet with 3 columns each declared, called date, place and avspeed
I'm trying to average the avspeed if it falls under certain criteria within the other 2 columns.

The dates are in day format and basically I require the average over the week

If tried using
=AVERAGE(IF(WEEKNUM(date)=45,IF(local=I4,avspeed)))

also declaring it as an array formula but both

also tried the following by summing and then counting the data and dividing one by the other, but so far neither are working

View 9 Replies View Related

Math Averaging

Jul 7, 2006

Trying to compute annual change I've always used the formula "(b1-a1)/a1" - where b1 is the current year, a1 is the prior year. I've been asked to compute average annual change over a number of years and thought simply by using the above for each year and then taking the average of all these, I'd be ok. I recently found a formula as follows: "(y2/y1)^(1/n)-1", where y2 is current year, y1 is 1st year I have data, n = the number of changes.

View 7 Replies View Related

Averaging Events

Apr 29, 2007

I have a spreadsheet,3 columns are shown in the attached.The first column gives the date of the event,the second column the time and the third a rating.I want to put the average rating for each event in each cell in column 4.

View 9 Replies View Related

Averaging Rows Of Data?

Jun 26, 2013

I would like to find the average of a column of data where the rows of data changes where my starting cell is always B53 but the end row of data can change after each simulation. The average function to be displayed at the bottom of the last row of data.

View 4 Replies View Related

Averaging Range With Lookup

Mar 21, 2014

TimeForce
00.2
0.50.5
11
1.52
24
2.54
34
3.54
44
4.54
54
5.54
64
6.51
70.3

for example i have this data I want to average range of some values in force column for which i only know range of values in TIME column..

For Example I want to average the values for which the values in time column are 0 to 1.5 ((0.2+.5+1+2)/4)

I deduct a formula but it is giving error AVERAGE(LOOKUP(0,A:A,B:B):LOOKUP(1.5,A:A,B:B))

View 7 Replies View Related

Averaging Formula For Time

Mar 13, 2009

I have a column with times displayed in this format "0 day 03 hour 32min"

Is there a formula that allows me to average a column of these times and retain the same format?

View 9 Replies View Related

Averaging 2 Drop Down Lists Into A Value

Jul 15, 2009

I read this thread which helped me very much. So I was able to link one drop down box to a list, which fills in the adjacent box. Now I need some help getting an average.

Ex. Drop down 1 has a list of names which applies the associated numerical value in box 3. Drop down 2 has a list of the same names with the same associated values, and I need these 2 values averaged and put in box 3.

The problem is that Drop down 2 doesn't always have a name. It may be left blank and so I need drop down 1 to continue applying to box 3 when drop down 2 is empty.

This is what I have for drop down 1 to apply to box 3.

=IF(C2="","",VLOOKUP(C2,Sheet2!A3:B18,2,FALSE))

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved