Re: Data in table, may need to convert to columns with OFFSET?


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" <Ron.H.1t0pub_1122786301.5815@xxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:Ron.H.1t0pub_1122786301.5815@xxxxxxxxxxxxxxxxxxxxxxxx
> >
> > 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?action=getinfo&userid=9749
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=391591
> >


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

.

Relevant Pages

  • Re: Data in table, may need to convert to columns with OFFSET?
    ... Biff ... I should have known that array formulae were ... > I used that to then create a pivot table from my data to find max, min, ... >> If you want the corresponding angle for the MIN or MAX of any SINGLE ...
    (microsoft.public.excel.misc)
  • Re: how to return mulitple corresponding values
    ... thanks for the input roger. ... The argument for the SMALLfunction that Biff proposed was ROW ... Try this: (array entered) ... i have a sheet listing jobs scheduled by managers. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: how to return mulitple corresponding values
    ... Morning Biff. ... this is my exact formula: ... it looks like we're mutiplying rng3 x corresponding value in rng1. ... Try this: (array entered) ...
    (microsoft.public.excel.worksheet.functions)
  • Re: puzzle
    ... >> "If the array may be rearranged then we can again find the singleton ... >> value and then partition the array into elements smaller than the ... >> pivot, equal to the pivot, and larger than the pivot. ... Richard's algorithm is making use of finding the median ...
    (comp.programming)
  • Re: Find value in array
    ... Biff was referring to the fact that there was a simpler formula (which you ... Bob Phillips ... While Biff suggested INDEX/MATCH, interestingly enough another expert ... Entered as an array using the key combination of CTRL,SHIFT,ENTER: ...
    (microsoft.public.excel.worksheet.functions)
Loading