EXCEL AVERAGEIF FORMULA

[scribbler99]

Hi Guys, please can someone enlighten me in what I presume, to the professional out there, requires a simple solution.
I have two columns on an excel 2007 spreadsheet that I am trying to get the average on and exclude any blanks.
my formula that works for one column is =AVERAGEIF(H10:H36,"<>0") but I cannot get this to work =AVERAGEIF((H10:H36,Q10:Q29)"<>0")

I've looked through the forum and found various averageif examples but couldn't find the exact same problem.

 

[SkipVought]

Hi,

Designed for only ONE column AFAIK.

And this makes sense, when you consider that each column contains related values, like the quantity ordered, while another column might contain the unit value and yet another might be the extended value. An average of more than one of these columns would not make sense.

 

[scribbler99]

When browsing for an answer to the above I saw where others had similar problems but spanning tabs. I read what you say SkipVought but have uploaded an image so that you can see what we're after. The presentation of this spreadsheet was the reason not all data was in the same column. The cell with the green background was to be the result.

[URL unfurl="true"]https://res.cloudinary.com/engineering-com/image/upload/v1430140694/tips/COST_SHEETS1_nvh6us.pdf[/url]

 

[zelgar]

You can't use an AVERAGEIF because the range is not in one Row/Column. You'll need to do something like this:

=(SUMIF(H10:H36,"<>0")+SUMIF(Q10:Q29,"<>0"))/(COUNTIF(H10:H36,"<>0")+COUNTIF(Q10:Q29,"<>0"))

 

[SkipVought]

This illustration represents a significant issue that often faces novice spreadsheet users.

USING A REPORT FOR DATA ANALYSIS IS FROUGHT WITH DANGERS, TOILS AND SNARES.

Excel data analysis features are designed for use in TABLES, rather than reports. Your report "snakes" the table into two columns AND inserts empty rows, BOTH of which throw analysis techniques into confusion.

It would be much more advisable to construct a proper table for the data, from which you analysis could quite easily be performed, and derive your report from the data in the table.