Re: [MLUG - DISCUSSION] Excel tricks

["Jonathan King" <EMAIL:PROTECTED>]

On 11/8/07, Mike Miller <EMAIL:PROTECTED> wrote:
> On Thu, 8 Nov 2007, Jonathan King wrote:
>
> > On 11/8/07, Mike Miller <EMAIL:PROTECTED> wrote:
> >
> >> I want to do this in Microsoft Excel but I'll also want to do it in
> >> Calc (Open Office), so feel free to tell me the answer in Calc terms,
> >> if you happen to know the answer and are willing to share.
> >>
> >> I want to use a value in a cell to determine a range for a function.
> >> I thought I used to do this in Lotus 1-2-3 20 years ago, but I'm not
> >> sure. So I think it is doable.  For example, I might want to sum the
> >> values in column B from row 1 to row 'X' where X equals the value in
> >> cell A1.  Any ideas?
> >
> > Lord help me; I know the answer to this question. I have too thoroughly
> > mocked that which I have become.
>
> If only you had sent the answer from your Blackberry!

Ha, ha.

> > The answer is in the handy "indirect" function. So let's assume a1 has
> > the ending row number, b is the row to sum (from b1) and c1 is where you
> > want the formula to pop up. Then what you want in c1 is:
> >
> > =sum(indirect("b1:b"&a1))
>
> That is fantastic.  Thank you!!
>
> I wonder if it works the same in Calc.  I will be finding out soon enough.

It also works in Google Spreadsheets, so I'm guessing it has to work
in docs. I think this is a situation where excruciating compatibility
is likely to rule the day.

> By the way, my reason for doing this is that I am creating a spreadsheet
> that will generate data for a nuclear family under a certain genetic model
> and then analyze those data several different ways.  It provides an easy
> way for students to get a feel for how different kinds of data and
> different statistical methods work to allow us to find the genes that
> influence a trait.  I will make it freely available when it's finished (or
> sooner for Jon, if you want to see it).

I don't have time this week, but it does sound potentially interesting.

jking

_______________________________________________
discussion mailing list
EMAIL:PROTECTED
http://mlug.missouri.edu/mailman/listinfo/discussion