Using this method the ranges must be the same size *and* shape.

But then again, you could always transpose if needed:

=AVERAGE(IF(CHOOSE({1,2},A1:A5,TRANSPOSE(D1:H1))=" x",CHOOSE({1,2},B1:B5,TRANSPOSE(D2:H2))))

Biff

"T. Valko" wrote in message
...
Can you provide a link to that discussion?
This is the one.
http://tinyurl.com/yn64g2

Ok, I remember reading that thread.

That thread also fits the evolved topic of this thread. Toppers says:

"There is a market for somone who could distill all the knowledge into a
single reference"

I think we agree but it would be a lot of work!

As for the subject of that thread, here's another formula to "do stuff" on
non-contiguous ranges:

Array entered:

=AVERAGE(IF(CHOOSE({1,2,3},B6:B19,B27:B40,H6:H19)= "x",CHOOSE({1,2,3},C6:C19,C27:C40,I6:I19)))

Using this method the ranges must be the same size *and* shape.

Biff

"Bob Phillips" wrote in message
...

"T. Valko" wrote in message
...
I agree it falls well short of discussing advanced techniques such as
the one we discussed back on 25th June

Can you provide a link to that discussion?


This is the one.

http://tinyurl.com/yn64g2

You won't find it anything new, but it was new to Joe (who by his own
admission is an advanced user) at that time, and the discussion was the
interesting part. At least I thought it was relevant to this thread.


I don't know what "that topic" is but I've also "thought about writing a
book" on "advanced formulas". I wouldn't know where to start! I haven't
a clue about how to write a book! I don't have JW's book, but does it
cover the types of formulas and techniques we routinely provide in these
groups? I have a couple of old books but they're pretty much useless for
anything above the very basics:


That topic was advanced Excel techniques, not just formulae, but mainly
formulae driven.

It's a while since I have looked at John's book, but as I recall, it
wasn't going into the depth of some of the solutions of the type that we
offer here. It was more along the lines of taking themes, and developing
them, showing the formulae available, how to use them etc. There is some
extremely basic stuff there, some more advanced stuff (Part IV is Array
Formulae). The best bit is part III for me, Financial Formulae, but
probably because I know naff all about them. Despite what Joe says, I
think it is well formatted, I think that is one of John's strengths.

I think the market needs a book that starts at the intermediate user, and
gives them tools and techniques to become an expert. No basic stuff, all
that is assumed in the reader. I really don't think that book exists at
this point. And of course, if anyone wants to write it, publishers now
want it to be focussed on Excel 2007.