Problem using AVERAGEIF to eliminate zeros

[ColleenMarie47]

Good Morning,

I am trying to use the following formula and I keep getting a #VALUE ! error:

AVERAGEIF(Firm1:Firm70!E46, "<>0")

I would like to get an overall average for a particular survey question, but I want to exclude zeros. I have each firm's submission in a separate sheet. Any clue what I am doing wrong?

[63falcondude]

Hello and welcome to the forum.

You can't use a 3D reference with AVERAGEIF, SUMIF, COUNTIF, etc.

See here for a list of the functions that can use a 3D reference:
https://support.office.com/en-us/art...ID=HP010102346

[JohnTopley]

Attach a sample workbook (not image).

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.

[JohnTopley]

Try

=SUMPRODUCT(SUMIF(INDIRECT("'" &Sheets &"'!E46"), "<>0"))/SUMPRODUCT(COUNTIF(INDIRECT("'" &Sheets &"'!E46"), "<>0"))

where Sheets is a named range of all your sheets (Firm1,Firm2......Firm70)

[63falcondude]

I have each firm's submission in a separate sheet.

This is not a good way to keep your data. If you plan on doing anything with your data (like you are here), I HIGHLY recommend re-structuring your workbook.

There should be one worksheet with all of the data on it.
This worksheet should have all of the survey questions in a separate column, all of the firms listed in separate rows, and each firm's answer to each question in the correct cell (wherever the row and column intersect).

[Richard Buttrey]

This is not a good way to keep your data. If you plan on doing anything with your data (like you are here), I HIGHLY recommend re-structuring your workbook.

I couldn't agree more.

A lot of people start by designing the workbook that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains all the columns and data from your individual sheet and with an additional column that holds the Firm name on each row. You could quickly create a macro that loops through all sheets and copies and pastes the data to the single sheet database.

[daddylonglegs]

https://support.office.com/en-us/art...ID=HP010102346

It's not on that list but FREQUENCY function also accepts a 3D reference, so assuming there are no negative numbers in the data you can get the average ignoring zeroes like this:

=SUM(Firm1:Firm70!E46)/INDEX(FREQUENCY(Firm1:Firm70!E46,0),2)