That was it!! Biff saves the day again!

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



"T. Valko" wrote:

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

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

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)"
m wrote in message
...
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



"T. Valko" wrote:

I'm trying to do everything with formulas.

So that means you're not using DataFilter?

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:AD1 0<=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)"
m wrote in message
...
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" wrote:

If you're using DataFilter 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
wrote in message
...
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.