Re: [MLUG - DISCUSSION] Excel tricks

[Mike Miller <EMAIL:PROTECTED>]

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!


> 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.

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).

Mike

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