Free Microsoft Excel 2013 Quick Reference

# Calculate the average of a row disregarding all 0 values

Hello Team,

I really appreciate what you are doing for us, and you've always been so helpful.

Here is my question:

If there a way to calculate the average of a row disregarding all 0 values?

for example, I have the table below:

Week5 Average 31-janv. 1-févr. 2-févr. 3-févr. 4-févr. 5-févr. 6-févr.

108 147 77 145 466 161 575 240 950 1 745 154 130 580 0 0 508 1 218 850 4 480 1 423 332 0 0 1 186

What formula shall I use so that when a 0 value is inserted, the average formula will disregard it and also calcule the average of the cells greated that 0.

Thank you so much,

Jeremie.

## Related Results

### Calculating the AVERAGE of a column omitting cells = 0?

For example, if I want to calculate the average of column 'B', I would have...

=AVERAGE(B3:B33)

Some of those cells in that column are zero (which in my case would be a weekend or holiday). If I wanted to get an average, not including non-work days, how might I omit cells that =0?

Is that possible?

### Formula to calculate the average of a range basing on condition

Hi,
I have a situation like this below..
18-Feb-02 02-Jun-04
26-Jul-02 08-Jun-04
29-Jul-03 10-Jun-04
22-Oct-03 05-Jun-04
01-Nov-03 10-Jun-04
11-Nov-03 14-Jun-04
18-Nov-03 04-Jun-04
20-Dec-03 21-May-04
30-Mar-04 10-Jun-04
08-May-04 22-Jun-04
29-May-04 15-Aug-04

11-Jun-04 11-Jul-04
12-Jun-04 16-Jun-04
22-Jun-04 28-Jun-04
24-Jun-04 26-Jun-04

10-Dec-04 23-Dec-04

28-Aug-04 01-Sep-04
28-Sep-04 29-Sep-04
08-Dec-04 22-Dec-04

20-Aug-04 29-Aug-04

25-Aug-04 31-Aug-04
30-Aug-04 10-Sep-04
03-Jan-05 10-Jan-05
27-Sep-04 05-Oct-04
30-Sep-04 07-Oct-04
03-Oct-04 04-Oct-04

The above are two columns of excel. I want to calculate the average of the
difference for each month in another work sheet like bwlow

Month Difference..

Can some one help me on how I can do that

### Calculating the average of 5 values and dropping the lowest

I need to calculate the average of 5 values while dropping the lowest value from the average for example;

----B----C---D---E---F-----G
4 | 80, 90, 70, 65, 32 Average(excluding 32)
5 | 64, 95, 55, 0, 60 Average(excluding 0)

I need a way to get the average of the highest values while excluding the lowest value and I have to do it for several rows so typing out a specific equation for each wouldn't be optimal.

### Averaging the sum of a row

I searched the forums and found a lot of things on averaging, but nothing I could use to help me with my problem. I need to figure out how to divide the sum of a row by the number of columns in a row, not counting 0's. Here's an example:

B3-G3
6 4 0 12 -4 -1

H3 (=SUM(B3:G3))=17

Now I can't figure out how to take H3(17) and divide it by the number of non 0 columns (5). I was just manually doing this, but it's becoming a pain and there has to be an easier way. Any help is greatly appreciated. Thanks in advance.

### How to calculate the average of minimum values

I have an Excel 2010 worksheet that contains dates in one column and hourly temperatures in another column. The data is for about ten years - so the worksheet contains about 75 000 rows.
How can I calculate the average of the minimum temperatures for a specific day (say for 20 March) ?
Or an average maximum temperatures as a matter of fact.
I use day of the year for the dates.

Regards and thank you.

### Charts: Calculate the area of a curve on a graph

Hello one & all,
I have just joined this group & i have a question that I would love a solution to...
I have a graph that contains mulitle lines & curves etc.. what I want to be able to do is figure out how to calculate the "Area of a curve on a graph" in excel, just hoping that somebody out there may have a solution. I can do it on Paper but can't figure it out in Excel...
Waiting with anticipation
Kind Regards
Mary FOley

### Find the end of a Row (Last Column)

I am trying to find the end of a row (the last column) and select all this data in that row and use it in a Form as a dropdown (combo box).
This is the code I have so far:
Code:
```Private Sub UserForm_Activate()

endrow = Sheets("Raw Data").Range("IV1").End(xlLeft).Select
Me.ComboBox1.RowSource = "'Raw Data'!A2:" & endrow
Me.ComboBox1.ListIndex = -1

End Sub```
I can get this to work if I want to go down a column for example:
Code:
```Private Sub UserForm_Activate()

endrow = Sheets("Raw Data").Range("C65536").End(xlup).Select
Me.ComboBox1.RowSource = "'Raw Data'!A2:A" & endrow
Me.ComboBox1.ListIndex = -1

End Sub```
But I cannot get it to go across the spreadsheet a,b,c,d etc.

Any suggestions?

### Find the average of a range that grows

Is there a non-VBA way to find the average of a range from A2 down to the last cell containing data?

This does not work at all but something similar to this?

```

```

### Calculating the average of cells

I am trying to use a formula to calculate the average of random cells
currently containing data, however also including cells where data will be
added at a later date but remain empty. Is this possible?

### Calculating the Averages of Quintiles

I have a list of 302 values, i am sorting it from least to greatest, and I need a function that can calculate the average of the 5 quintiles, basically the average between 0 - 20%, 20% - 40%, 40% and 60%, 60% and 80%, 80% and 100%.

The caveat here is, 302 is not easily divisible by 5.

### Calculating the average of every value in every, say, 12th cell

I have to calculate the average of the prices of hundreds of products, per year. The problem is that the data is laid out in such a way that product name and year are both on the X-axis! For example, in cell b2 price for product X in 1995, in cell B3 price for product X in year 1996 and so on. Is there any way to tell Excel to simply calculate the averages of, say, every 12th cell in column B?

Thanks!

### Change the colour of a Row when a condition changes in a cell

Hello

I have a workbook with several sheets in Excel 2003 and was using conditional formatting to change the colour of a Row when a certain condition was met. As an example if I selected "C" from a drop down list it would change the colour of the row to orange. it worked fine but now I have more than 3 conditions so I needed some code to allow for 4 or 5 conditions.

I found this code and adapted it for my spreadsheet but it just won't work! I have also disabled all the conditional formatting that I had in each sheet. I tried putting the code in the "workbook" and no luck so I have the code in every sheet but it still won't work. ANY help would be really appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

Set VacancyListOfferWoods = Range("A2:AY300")

For Each Cell In VacancyListOfferWoods

Select Case Cell.Value

Case Is = "C"
Cell.EntireRow.Interior.ColorIndex = 7

Case Is = "F"
Cell.EntireRow.Interior.ColorIndex = 8

Case Is = "DB"
Cell.EntireRow.Interior.ColorIndex = 4

Case Is = "Q"
Cell.EntireRow.Interior.ColorIndex = 3

Case Else
Cell.EntireRow.Interior.ColorIndex = xlNone

End Select

Next
End Sub

### Calculating the average of every Nth value.

Problem:

Calculating the average of the values in every 4th line.

Solution:
Use the MOD and ROW function to find every 4th cell in your list.
Calculate the average of those numbers using this array formula:
{=AVERAGE(IF((MOD(ROW(\$B\$2:\$B\$10)-11,4))=0,\$B\$2:\$B\$10))}

Serial Number___Value
1_______________20
2_______________40
3_______________50
4_______________30
5_______________8
6_______________400
7_______________35
8_______________10
9_______________100

Result: 20

### Calculate the average by ignoring the zeros

Hi,

Is there a way in excel to calculate the average of a coulmn but if any entry is zero ignore it. Let say I have column x = {10,0,1,0}. The average should be 11/2

Ali

### Calculating the average of a column based on multiple conditions

How do I calculate the average based on multiple conditions? Is my syntax incorrect?

Here's what i'm looking for:

=average((IF(X1:X1000=A1)*(Y1:Y1000=B1),AA1:AA1000))

what am i doing wrong here?

### I want to calculate the average of best 5 out of 7 numbers?

How can I calculate the average of 5 best out of 7.. or best 10 out of 15..
For example if I have 10 different numbers. 10,7,9,8,5,6,7,5,6,7 but I only
want it to take the average of the best 7.. I really appreciate your help..
Thank you!

### Changing the height of a row is very slow

Hi,

On some computers, changing the height of a row in a given document is
very slow (about 2 seconds), on other computers with the same document
it is almost immediate. I haven't notice any difference between these
computers. The strange thing is that a few months ago, changing the
height of a row was immediate on every machines.
This slow down is critical, because i use the changing of the height in
a macro that takes 2 minutes more to run on the slow computers, while
it takes only 5 s on the fast computers.
I can reinstall Office, but i prefer to know what's wrong in the
installation or in the document. I would avoid to reinstall Office
every few months if the slow down appears again.

Thanks in advance for any help or suggestion.

Regards,
Marc

### How do i calculate the average of 30+ cells over multiple workshee

how do i calculate the average of 30+ cells over multiple worksheets?

### How to calculate an Average of a count

Hi peers,

I have tickets numbers in this format (111122 , 222333,..etc) , those Tickets numbers are not considered as numbers.
what i am able to do is to count those tickets monthly / quarterly which is OK..
but what i need is how to : calculate the average of the count Quarterly = count (month1)+count(month2) + count (month3) / 3 ..

Any help will be appreciated

thanks

### Calculating the volatility of a stock

I need to calculate the volatility of a stock.
I have historical prices
need an excel formula
Thanks!

### Finding the sum of a row - the lowest 3 values

Hi there,
I am trying to figure out how to find the sum of a row minus the lowest
3 scores for a tournament I am running.
Basically we have points for each race and at the end of the season we
drop the 3 worst scores. Can this be *easily* done in Excel?
Cheers,
Nathan

### Conditional Formatting a cell Based on the average of a range

I need to conditional format cells to change red if they contain a value which is 2 or more points lower than the average of a range of point scores and green if they are 2 or more points higher than the average of the same range. Anyone got any top tips please?

### Can't modify the format of a row

Hi,

I'm trying to change the format of a row but it's impossible. I select a background color but after saving the changes I close the workbook and I open it again. The changes I made disappeared. The format of the row is the same before modifying it.

I think the worksheet is protected, but I don't need to introduce any password. I know Excel protects the cells by default (I know it because when I select format cells --> protection tab by clicking right mouse button I see the blocked check box selected) but I don't know what is the meaning exactly, because in my workbook I can change the content of a cell or row but no the format (for instance background color).

Why I can't change the format of a row?

Thanks.

### How do I calculate the age of a subject based on their DOB?

I want excel to calculate the age of a subject by entering the date of birth
of such subject