[Attarderb]

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.

[cwgwowcom]

[TeemFilla]

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?

[Cheaperisdeeper]

Do AVERAGE(A1:A1000000000000000000000000000)

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

edit: maybe not 10000000000000000000000000
something more realistic

[JesikaFclq]

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

[Sierabiera]

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?

[leadmoffer]

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

[mXr8icOB]

I generally add in the middle or towards the bottom

[Enladalusange]

Let me rephrase it then. The TRIM column is filled by values that were fetched by a VBA procedure. These numbers are quarterly rate of returns. But I also want to fill the YTD (year-to-date) and Début (beginning) columns with the corresponding rates of return, which need to be calculated from the quarterly data.

Thinking about it now, the easiest and fastest route probably would have been to compute everything with the subroutine, instead of fetching some numbers and then calculate the rest with a spreadsheet formula.