Need help averaging a range using a diff column as criteria

[Bryan (aka The Perfectionist)]

I've been working on this for hours, and I desperately need help.

I'm simplifying the spreadsheet immensely, but the heart of my problem is
this: In my spreadsheet of apartment buildings, the # of apartments in a
building is in column AD, and rent per apt. unit is in column BB:

AD BB
228 800.00
450 880.00
964 870.00
290 760.00

I've been asked to create a summary table which calculates the average rent
per unit for various ranges -- for instance, the average rent per unit for
buildings with 100 to 299 apartments, 300 to 499 apartments, etc.

So I need to filter rows in column AD to show just the buildings within a
range (100-299 apartments, for example), then sum column BB for only those
AD-filtered rows, then average column BB for only the AD-filtered rows. I've
read hundreds of posts and tried dozens of combinations of SUMIF, COUNTIF,
and SUMPRODUCT, and I'm just not getting there.

Please save me!!!

A million thanks.

 

[T. Valko]

If you're using Data>Filter then you want to use the SUBTOTAL function to ge
the average of the visible rows. If you're filtering on column AD then:

=SUBTOTAL(1, BB2:BB100)

1 is the index number for average. See Excel help on SUBTOTAL for other
index numbers and what they mean.

Just make sure you put the formula outside of the filtered rows. It's
usually a good idea to put the formula above the filter.

--
Biff
Microsoft Excel MVP


"Bryan (aka The Perfectionist)" <Bryan (aka The
Perfectionist)@discussions.microsoft.com> wrote in message
news:[email protected]...

 

[Bryan (aka The Perfectionist)]

Thanks for the response. I should have mentioned, the summary table is on a
different worksheet than the raw data, so I'm trying to do everything with
formulas.

 

[T. Valko]

I'm trying to do everything with formulas.

So that means you're not using Data>Filter?

Not a problem!

To average the rent on bldg's with >=100 units and <=299 units:

Array entered** :

=AVERAGE(IF((Sheet2!AD1:AD10>=100)*(Sheet2!AD1:AD10<=299),Sheet2!BB1:BB10))

Better to use cells to hold the criteria:

A1 = 100
B1 = 299

=AVERAGE(IF((Sheet2!AD1:AD10>=A1)*(Sheet2!AD1:AD10<=B1),Sheet2!BB1:BB10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Bryan (aka The Perfectionist)"

 

[Bryan (aka The Perfectionist)]

The formula is calculating (major progress), but the result is far too low
(about 20% of what it should be). If I replace the "*" in your formula with a
"-" (which makes more sense to me but could be wrong), the result is closer
but still not correct.

Would this formula omit blank cells? Not all buildings have data in every
cell. I need to calculate the average of cells *with data* in BB based upon
the selection of rows from AD.

I'm totally confused.

Your help is most appreciated.

Bryan

 

[Pete_UK]

Hey Biff,

I notice you've just reached 10,000 posts in the All-time GG archive
for this group - Many Congratulations !!

Pete

 

[T. Valko]

Thanks, Pete!

--
Biff
Microsoft Excel MVP


Hey Biff,

I notice you've just reached 10,000 posts in the All-time GG archive
for this group - Many Congratulations !!

Pete

 

[T. Valko]

Would this formula omit blank cells?

Not all buildings have data in every cell.

That could lead to incorrect results if you had something like this:

228 800.00
450 880.00
964 870.00
290

290 has a corresponding empty cell so that cell is evaluated as 0 and is
included in the average.

To account for that (still array entered):

A1 = 100
B1 = 299

If I replace the "*" in your formula with a "-"
(which makes more sense to me but could be wrong)

No, you don't want to do that!

We're using "*" to multiply the arrays together and we'll get a result of
either 1 or 0. Where all 3 conditions are TRUE the array multiplication will
return a 1 and where the array multiplication =1 it includes the
corresponding cell from BB in the average.

--
Biff
Microsoft Excel MVP


"Bryan (aka The Perfectionist)"

 

[Bryan (aka The Perfectionist)]

That was it!! Biff saves the day again!

A million thanks -- you have no idea how grateful I am.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bryan (aka The Perfectionist)"