Glad to help. Thanks for the feedback!

Biff

"Ron H" wrote in
message ...

Biff,
Very elegant. Thank you. I should have known that array formulae were
the way to go. Thanks for everything!

I had come across another way late last night. On John Walkenbach's
site he has a tip that is essentially a "reverse pivot" whereby you
create a database table from a summary table.
http://j-walk.com/ss/excel/usertips/tip068.htm

I used that to then create a pivot table from my data to find max, min,
and average. But to do so, I need to group data along the x or y axis
and then select which intervals I want included in the pivot table.
Your way is more elegant, simple, and direct, and makes one use one's
mind!

Biff Wrote:
Hi!

OK.....

These formulas all use references based on the size of the sample you
posted. A2:A7 for the angles and B1:F1 for the energies. All you need
to do
is change the references to suit your actual table.

First thing.....

Give the "data" section of your table a name.

Select the range B2:CC70

In the Name Box type in something like Tbl. The name box is the little
box
at the far left side of the formula bar. It shows what cell is
currently
selected. Just click inside that box and type Tbl.

Now, you need 4 cells to hold the variables that you want to use for
the
calcs. In these examples I'll use:

Energy
A10
A11

Angle
A14
A15

So, if you wanted to find the MIN, MAX or AVG for energies 2 to 4 for
all
angles:

A10 = 2
A11 = 4

Formulas entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MIN(IF(B1:F1=A10,IF(B1:F1<=A11,Tbl)))

=MAX(IF(B1:F1=A10,IF(B1:F1<=A11,Tbl)))

=AVERAGE(IF(B1:F1=A10,IF(B1:F1<=A11,Tbl)))

If you wanted to find the MIN, MAX or AVG for energies 2 to 4 for
angles -3
to 2:

A10 = 2
A11 = 4
A14 = -3
A15 = 2

Formulas array entered:

=MIN(IF(A2:A7=A14,IF(A2:A7<=A15,IF(B1:F1=A10,IF( B1:F1<=A11,Tbl)))))

=MAX(IF(A2:A7=A14,IF(A2:A7<=A15,IF(B1:F1=A10,IF( B1:F1<=A11,Tbl)))))

=AVERAGE(IF(A2:A7=A14,IF(A2:A7<=A15,IF(B1:F1=A10 ,IF(B1:F1<=A11,Tbl)))))

If you want the corresponding angle for the MIN or MAX of any SINGLE
energy:
For example, energy 3:

A10 = 3

You would probably want to use a different cell to hold this variable
but I
just used A10 as the example.

Normally entered:

=INDEX(A2:A7,MATCH(MIN(INDEX(Tbl,,A10)),INDEX(Tbl, ,A10),0))

=INDEX(A2:A7,MATCH(MAX(INDEX(Tbl,,A10)),INDEX(Tbl, ,A10),0))

If you'd like a sample file to study this I'll be glad to put
something
together. Just let me know how to contact you.

Biff

"Ron H" wrote in
message ...

If it can be done without Pivot Tables I would gladly accept help
with
formulas.
I just thought that Pivot Tables would be the quickest way to check
the
values within a range, by using the grouping function.


--
Ron H

------------------------------------------------------------------------
Ron H's Profile:
http://www.excelforum.com/member.php...fo&userid=9749
View this thread:
http://www.excelforum.com/showthread...hreadid=391591



--
Ron H
------------------------------------------------------------------------
Ron H's Profile:
http://www.excelforum.com/member.php...fo&userid=9749
View this thread: http://www.excelforum.com/showthread...hreadid=391591