Stuck in Excel

[Fake Boris]

I'm sure this one is workable, but I can't seem to get the correct combination.

I have data separated by quarters. For each quarter, there are 4 columns. I want a formula in the column YTD that will multiply every TRIM value in the same row whose date is smaller or equal to the date over the corresponding YTD cell and whose year is the same.

[Fake Boris]

It should be easy enough, but the main difficulty is to find a way to avoid a circular reference, because obviously Excel wants to verify if the cell in which I enter the formula fits the criterion. And that cell appears to be in the same row as the data to multiply.

[Fake Boris]

Got it! the trick to avoid the circular reference was to limit the range where the logical check is applied with a combination of offset+match.

Anyway, thanks for the help...

[Saras]

[Japher]

you rock Saras

[Eli]

I also have an Excel question.

I have a list that's constantly growing. Now it's from, say, A1 to A45.

I also have a bunch of statistics i'm deriving from this list. For example: AVERAGE(A1:A45). Now, every time I add another cell to list, I have to go over all the statistics cells and update A45 to A46 in every one of them. I have a hunch that this is not the most efficient method.

Is there a way to get all the statistics cells to update automatically whenever the list grows?

[Saras]

where do you add it? if you insert it at the top, leave one cell blank and insert your data points under it, the formulas will expand.

[Japher]

Do AVERAGE(A1:A1000000000000000000000000000)

if there's nothing in the cell, it won't use it

edit: maybe not 10000000000000000000000000
something more realistic

[Eli]

where do you add it?

At the bottom. :/

[Stuie]

Originally posted by Japher
Do AVERAGE(A1:A1000000000000000000000000000)

if there's nothing in the cell, it won't use it

edit: maybe not 10000000000000000000000000
something more realistic

A1:A65536

[Saras]

can you do an average for the whole column?

[Eli]

Originally posted by Saras
can you do an average for the whole column?

Uncomfortable, since some of the statistics are in the same column below the data, but I can move them. It will look less pretty but it'll work.

But wont the empty cells screw up the results?

[Stuie]

No - I just tried it and it only factors in cells that have a numeric value.

Edit: Oh, and you can just use =average(a:a)

[Fake Boris]

Originally posted by Eli
I also have an Excel question.

I have a list that's constantly growing. Now it's from, say, A1 to A45.

I also have a bunch of statistics i'm deriving from this list. For example: AVERAGE(A1:A45). Now, every time I add another cell to list, I have to go over all the statistics cells and update A45 to A46 in every one of them. I have a hunch that this is not the most efficient method.

Is there a way to get all the statistics cells to update automatically whenever the list grows?

Man, dynamic ranges are your friend. Most people don't know about it, but the OFFSET function can return ranges, and not only single cells. So when you define a name for a group of cells(Insert, Name, Define), instead of having the name refer to a fixed group of cells, have it refer to OFFSET(x,...)

The following link is a good introduction to the vast possibilities it offers: http://www.ozgrid.com/Excel/DynamicRanges.htm

[Stuie]

Good stuff! Thanks Boris!