Average column if adjacent cells are between a specific number range

**Madtaxi** · 06-19-2017, 06:15 PM

Hello,

I have a data set that includes the finished sqft and sales price of different housing products. I'm trying to create a second tab that shows the Average and median Sales prices and lot sizes of product that falls between a range of finished sqft. (eg 2000-2200).

I have been trying formulas like the following: =IFERROR(AVERAGEIFS(RPD_GridExportTran!S124:S732,RPD_GridExportTran!F124:F732,"<='SFD '!C29",RPD_GridExportTran!F124:F732,">='SFD '!B29"),"Error")
but get nothing but errors.

I'm attaching the spreadsheet here. The first tab has the product data, and the second tab "SFD" is the summary page where I'm trying to use this formula. I got to the first three product summaries by manually filtering the fin sqft column, but I'd like a formula that allows me to change the table values and updates averages and medians automatically.

Thanks in advance!

**FlameRetired** · 06-19-2017, 07:25 PM

In the attached find the square footage lower and upper boundaries broken out into columns E:F.

In D11 this formula

Formula:

Please Login or Register  to view this content.

In D12 this array entered formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

In D13 this formula.

Formula:

Please Login or Register  to view this content.

Then array entered in D14.

Formula:

Please Login or Register  to view this content.

**Madtaxi** · 06-20-2017, 10:32 AM

Thank you for the help! Could you explain why the "&" is required in the criteria section of the formula?

**FlameRetired** · 06-20-2017, 08:56 PM

That is the syntax of those arguments that the IFs family of functions "understands".

When comparing (<=, <, >=, >, <>) COUNTIF(s), SUMIF(s) and AVERAGEIF(s) require a concatenated string in the criteria. To the best of my knowledge these are the only functions that require this.

(If you're interested they also take wild cards ... (?*))

Sometimes these need to be concatenated as well.

Average column if adjacent cells are between a specific number range

LinkBack

Thread Tools

Rate This Thread

Display

Average column if adjacent cells are between a specific number range

Re: Average column if adjacent cells are between a specific number range

Re: Average column if adjacent cells are between a specific number range

Re: Average column if adjacent cells are between a specific number range

Thread Information

Users Browsing this Thread

Similar Threads

Merge cells with same number within specific column range.

[SOLVED] If number in Column A matches number in column B - Copy B cell and adjacent cells ?

[SOLVED] Highlight repeat cells in one column if cells in the adjacent column contain specific text

How can I hide certain adjacent cells if a specific cell is in a range value.

Average range of cells in column if values in adjacent column are equal

Counting Names in one column if the adjacent column contains a specific number

Average non-adjacent cells in column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions