Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

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.


Post your answer or comment

comments powered by Disqus
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?

Thanks in advance!

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

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.

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.

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.

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

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?

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?



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?

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.

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!

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

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

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

Thanks for your help.

Ali

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?

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!

Eyad

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 worksheets?

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

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

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

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?

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.

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


No luck finding an answer? You could always try Google.