| Author | Message | ||
     Pwnzor |
I need to get the average of all the values in a particular column that gets longer every day... without having to change the formula every time I add a field to the bottom of the column. =AVERAGE(B2:B150) - I need this to ignore the blank fields in the column, and only average everything with a value above zero. | ||
| Socoken |
Can you try =AVERAGE(B:B) In just experimenting, it works for me with Excel 2016. | ||
| Pwnzor |
Still getting the division by zero error...  | ||
| Court |
You can do a "count" of the column. It will count the number of entries. Exp: 5 8 9 Count = 3 There's your denominator. Sum the column for your numerator and do the math. | ||
| Hootowl |
Average (b: b) should work. But you can also try averageif where the criterion is >0. (Message edited by Hootowl on March 19, 2016) | ||
| Sifo |
I tried it in Excel 2010 and it works. I do get the divide by zero error until there's numeric data in the column being averaged, but it looks like you have that.  | ||
| Macbuell |
Do an IFERROR function that will enter a value if there is an error. You can use "" to have the value be blank or "0" if you want it to be zero. I do this all the time to get rid of that stupid Div/0 error. | ||
| Sifo |
The thing I don't get is that he's got numeric data in column H. It SHOULDN'T be dividing by zero. IFERROR is just going to hide what's going wrong. | ||
| Macbuell |
He shouldn't be getting the Div/0 error in the average formula unless that error is already present in the MPG column. Use the IFERROR function for the MPG Calc and then the average formula should work just fine. At least that's what I think without looking at the actual worksheet. | ||
| Torquehd |
DON'T DIVIDE BY ZERO!!!  | ||
| Xdigitalx |
I wish I learned Excel years ago. | ||
| Pwnzor |
OK, I've got it... By using the IFERROR function in the column where the AVERAGE draws it's data, it eliminates the problem.  | ||
| Macbuell |
Cool. Glad it worked. |
