# Average - Cut Out Zeros

Jul 7, 2009Within a worksheet I have data like this in a column (actually, lots like it in many columns):

View 3 RepliesWithin a worksheet I have data like this in a column (actually, lots like it in many columns):

View 3 RepliesADVERTISEMENT

I am using a formula to include all values greater than zero in my average which has worked fine until i tried to extend the parameters, then it gives me the Value? sign. Does anyone know another way to accomplish what i want?

here is what i am using now.

=average(if(BZ28:CM28<>0, BZ28:CM28, " "))

i was trying to make the CM extend to CS, but for some reason it is not working.

Anyway, i figure a different equation might be better than what i

have and solve my problem.

I want to take an average of only non-zero numbers in a range of cells containing a few zeros. I know there is a function to do this, but I haven't been able to find it. With the normal average() function, the denominator is too large as cells with 0 are included.

View 9 Replies View Relatedim trying to average over a number of work sheets to determine the avergage talk time of employees over a day, ive tried:

View 14 Replies View RelatedI current have an Excel doc that I am trying to pull averages from. Every row has data that needs to be averaged but I only need every other cell. And I cannot have zeros in the average because it will skew the data. Also if the average for a row ends up being 0, I want that average to show up as 0 not #DIV/0!. From Column H to EE I need to average each row individually.

I came up with the below formula.

=IF(SUM(H1:H1:J1:J1:L1:L1)=0, "0", AVERAGEIF(H1:H1:J1:J1:L1:L1, ">0")

It works but Its not practical because I have to manually enter all of the cells that it pulls from.

I'm trying to return the average, min and max values of a range that will have errors, be blank or have zeros. I have already found an array formula that works getting rid of the errors but the zeros are now a problem.

{=AVERAGE(IF(NOT(ISERROR(D27:G27)),D27:G27))}

I was thinking that an OR might be needed but for the life of me can't figure out where it belongs. I will also be using this formula with both MIN and MAX in the adjacent cells so it would be great if all I have to do is swap those in for AVERAGE. I'm not keen on how array formulas work, just found an example similar to this on the interwebs, and I'm curious why if the array brackets are removed the value returned is 0 and not an error.

Workbook is proprietary so VB is locked and not an option (not sure if it was but wanted to be clear).

I've got a worksheet that has chronological quarterly data for which I'd like to do a 5-year rolling average. The layout is: Column A = Dates, Column B = Values, and Column C = 5-year Rolling Average. My problem is that I want to exclude zero-values from the averaging calculation. I tried the following:

={AVERAGE(IF(B1,B5,B9,B13,B170,B1,B5,B9,B13,B17))}

However, Excel will not accept this formula (I assume an array must be contiguous? Please let me know if that's correct).

I have a one column of numbers...let's say consisting of six rows

the following numbers 12,0,14,25,0,9

if I average this it comes out to 10

how can I average the same rows but exclude the zeros in the outcome(is there a formula to do that?)

I would like the answer to be 15.....I still need the zero's in the rows however

I am trying to make a excell spread sheet that will calculate my students averages for the year. I need to account for zeros in the coarse and I want this excel sheet to track the current average all year long . So I do not need to include my blanks in the average as I go. Also , how do I formulate my average accum to show this formula - Test scores, four of them count as 80% of the total grade. So each test is worth 20% / Lab 10% of total grade and homework is additional 10%. I downloaded the templete from MSN and have tweaked it to my liking except for the coding above. Please advise. I am a below par on Excel. My attachemtn is below of my templete. One note. the templete gave me the room to include 13 ros of homework - but I will not necessarily use all of them... I can make it one row for home work only ....

View 9 Replies View RelatedAttached is an xls with my formulas and problem. We need a way to factor in zeros in grading student workers. However, we also need a way to omit blank or null cells if the workers did not do a particular project. The formulas currently in the sheet compute zeros for both scenarios, lowering the overall 'grade' for workers who didn't do a project compared with workers who did the project but got a '0'

View 8 Replies View RelatedI have run into a problem with the array formula. After inserting formula with the CTRL+SHFT+Enter it is giving me the same sum to the 3 cells that I had array formulas in but trying to capture different data from what was in the capturing column. Example:

If row F consists of text types: Move-in, Mid Year, and Year End as potential options,and row G is the score for that text type(cells will consist percentages), give me the average of all the cells in row G that are specific to Move-In only, but don't include the blanks in the average.

My existing formula isn't designated to exclude the blanks. how to exclude blanks and how to get it to stop giving me the same result in the three separate cells. My current formula is as such: {=AVERAGE(IF(F2:F73=E76, H2:H73))}

I am working with averages of ACT scores and Class rank percentages. I cannot figure out how to exclude all the zeros and/or DNR (do not rank) values that are in those columns. For example if I have 10 items, and 2 are DNR, the I want to exclude the 2 DNR's in my average. I tried putting it in the Row or Column area and unchecking what I wanted to exclude and then drag it into the data area, but that didn't work either.

View 2 Replies View RelatedHere are my cells and values

B38 C38 D38 E38 F38 G38 H38 i38 J38 k38 L38 M38 N38 O38

0.00

None

1.95

16

1.73

21

0.00

None

20.10

20

1.73

20

0.00

None

What I would like to do is average only every other cell AND also not count zeros. So =average(B38,D38,F38,J38,L38) generally the idea not averaging H38 & N38 since they are both zeros.

excluding zeros and for excluding hidden rows, but can't seem to find a way to do both at the same time, which is what I need to do.

My company has projects that come and go (and are hidden when they are gone) and at times, those projects return 0s because we do not work on them for a short period... so, when I average a column, I need to exclude both situations.

I would like to average a non-continuous range of cells while also excluding all zeros.

I am averaging hours worked and the hours are found in cells:

B2, D2, F2, H2, J2, L2, N2

Some of these cells contain zeros at this time and I do not what to include the zeros in the average.

I have found formulas that would work but they are all with continuous cell ranges.

I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem.

Sub SumBlock()

Dim First_Row As Long

Dim Last_Row As Long

Dim iTotalRows As Long

Dim iCount As Long

iTotalRows = Range("A65536").End(xlUp).Row

First_Row = 2

Do While Last_Row < iTotalRows - 1

Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row

iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0")

ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount"

First_Row = Last_Row + 2

Loop

End Sub

This is info I have in a column

The formula in each cell is

=REPLACE(Track!B58,5,11,"") the reason behind this is that the info originally is entered into one cell like 25.0 x 32.0 and I wanted to use a part of the cell for column A and the other for column B.

25.032.025.032.125.032.225.032.325.032.425.032.525.032.625.032.725.032.825.032.925.032.1025.032.1125.032.12

My dilema is that I want to average the row which I have tried loads of formulas etc which are not working

I use this as sometimes not all cells are filled in so I want it to average only the data avaliable and discard any ZEROS.

=AVERAGE(IF(B28:B41>0,B28:B41))

However when I use this formula it is just coming up with

#DIV/0!

how to enter a correct formula so I can get the average.

I am trying to do the following.

Monday=0

Tuesday=5

Wednesday=10

Thursday=0

Friday=5

Saturday=0

Sunday=0

Averages

Monday=0

Tuesday=5

Wednesday=7.5

Thursday=0 (I have got a value return of 7.5)

Friday=5

Saturday=0 (I have got a value return of 5)

Sunday=0 (I have got a value return of 5)

I need to work out averages for cells higher than zeros, in other words, I need to ignore those.

Also say I have got an average of 5 by Tuesday and no number yet for the rest of the week, I still get an average of 5 for all days left which I do not want.

I am using excel 2003 and formula =SUM(RANGE)/COUNTIF(RANGE,">=0").

I would also like to hide the annoying #DIV/0! error.

with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.

What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.

Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.

I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?

Vendor’s Name

Jul 09

Aug 09

Sep 09

Oct 09

Nov 09

Dec 09

Jan 10

Feb 10

Mar 10

Apr 10

May 10

June 10

1

Vendor1

20

5

15

3

0

0

0

0

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.

=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?

TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)

If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)

The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.

Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.

I was going to create a summary page and reference column d.

The following just gives me the min, I want the min average if possible:

I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?

=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)

I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.

I'm using the formula =AVERAGE(B16:L16) to give me the average.

However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.

Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.

Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.

so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.

I have formatted the cells to be '0000' (showing the leading zero) however when my VBA code runs it copies the cell value into a string but when I stop the code running to check the value the leading zero(s) are gone. I can see they are the same on both worksheets in the correct format. I've tried a few others like integer and long but same same.

View 3 Replies View RelatedI have a CSV file I'm importing into Excell that contains leading zeros in a number field. I cannot get the zeros to display in Excel, but the user of the report needs to see them. Is there a way to dsiplay the leading zeros on a number field?

View 10 Replies View RelatedIs it possible to 'eliminate' the zero values when using =RANK? For instance, if you have data with values from +100 to -100 with some lines have '0' value, can they be ranked disregarding the zeero values.

View 3 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved