# How to Average Non-Null/0 value fields

1. ## How to Average Non-Null/0 value fields

I'm trying to average multiple cells, but I need the formula to not include null or 0 values into the average calculation. The tricky part though, is that the fields that need to be averaged are sparsely populated in a worksheet and are not in a single range.

For example, I need to average cells F7, H7, J7, M7, P7, V7. None of these cells are in a single range, and some of them will be 0's that I'll need to have excluded from the calculation.

2. ## Re: How to Average Non-Null/0 value fields

Something like this?
=AVERAGEIF(F7:V7,"<>0")

AVERAGEIF()

3. ## Re: How to Average Non-Null/0 value fields

That would normally work, except the cells/values that are between the cells that I need to include in the average, contain data that's not applicable. So using your example, not all of the cells between F7 and V7 are applicable. Some of them contain text and others contain amounts that don't have anything to do with the other cells in the range that do need to be included in the average calculation.

4. ## Re: How to Average Non-Null/0 value fields

So

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

5. ## Re: How to Average Non-Null/0 value fields

Attached is an example of what I'm trying to accomplish

6. ## Re: How to Average Non-Null/0 value fields

=AVERAGEIF(\$D\$3:\$M\$3,"\$ Change",D4:M4)

7. ## Re: How to Average Non-Null/0 value fields

I think that partially solves for it, except I'm needing to use that same logic across multiple columns.

8. ## Re: How to Average Non-Null/0 value fields

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
If you did it already - ignore it.
Thank you.

If you've another problem create new thread with logic description and attached excel file with reflected structure of your data (not a part of data)

9. ## Re: How to Average Non-Null/0 value fields

Try this
Enter formula in N4 and copy down
Formula:
`Please Login or Register  to view this content.`

There are currently 1 users browsing this thread. (0 members and 1 guests)