Evaluate command

[Don]

I received a suggestion to use the Evaluate function in a prior note
(subject "named range question") and it works well.

One problem, the evaluate command does not seem to recalculate when related
cells are changed. I have to stimulate that recalculation by clicking the
cell then clicking OK from the formula bar.

No information for the Evaluate function seems to be available in Excel
Help.

I tried F9 but it still does not recalculate.

Any ideas?

 

[Guest]

Post exactly what you are using in your spreadsheet. The issue that you are
running up against is how Excel's smart calc works. When one of the precident
cells of a formula is changed then the cell with that formula in it is tagged
as being dirty. The next time a calc runs it goes through all of the dirty
cells and recalc all of them. If the cell is not dirty then it is not
recalced. Because your function (whatever it is) can refer to any number of
ranges the dirty flag is not being set correctly. Indirect is a volatile
function (similar to the Now() function) which means that it is always dirty
and always recalcs.

 

[Lori]

As an alternative to volatile functions you can make the formula that
refers to the evaluate function explicitly reference the dependent
cells. For example if the defined name is Evaluate and the dependent
cells are A1 and the range B2:C4, you could use the formula:

=IF(1,Evaluate,(A1,B2:C4))

which would recalc whenever any of those cells changed.

 

[Roger Govier]

Hi Don

I think it was my suggestion to use Evaluate.
I can see your problem. I had only tested on changing the value in a
cell from one to two etc. to pick up a different named range - when it
does work. It doesn't work if you change a value within the range being
calculated

I tried F9 but it still does not recalculate.

Alt + F9 will do it.

However, I do not think this is the best solution for you. My use of
Evaluate (other than as a VBA function set to Volatile), has only been
relating to individual rows, where entering or amending a value does
trigger the re-calculation. I can see it being a problem with ranges
when used in this way.

Better to use the volatile function, Indirect() (which I normally avoid
where possible) but it will more suitable in this case

=SUM(INDIRECT("range_"&$A$1))
=AVERAGE(INDIRECT("range_"&$A$1))

where A1 holds your value One, Two etc.

 

[Don]

Thanks Roger, that works.

One problem, I want the range names (range_one, range_two, etc) to have
relative column addresses (eg,B$1) because I want to sum several different
columns. The relative column numbers don't seem to work with the indirect
method you gave me. Any ideas?

 

[Roger Govier]

Hi Don

Well, we could be really sneaky <vbg>
Go back to using Evaluate, but make it volatile by changing the cell
with your formula of
=SUM(myrange)
to
=SUM(myrange)+calc
after we had created a new named formula called calc = NOW()-NOW()

Because NOW() is volatile, adding it and subtracting it again, forces
Excel to carry out a recalculation, and any change of numbers within
your range are incorporated, as is any change in the name of the range
being used.

On the other hand, maybe there are simpler ways around your problem.
Perhaps we need to start from the beginning<g>
What is it that you wish to Sum, Average etc.?
Why are you using different named ranges?
Why are the ranges relative?

It might just be that we can use one name, and rely upon the
"relativity" to give the effect of range_two etc.
Post an example of the data and what you are trying to achieve

 

[Don]

1 17-Nov-00 7.53 71.73
2 15-Dec-00 6.03 0.00
3 19-Jan-01 6.00 2.31
4 16-Feb-01 5.74 0.00
5 16-Mar-01 3.90 0.00
6 20-Apr-01 3.51 6.67
7 18-May-01 3.64 0.00
8 15-Jun-01 2.87 0.00
9 20-Jul-01 3.04 0.00
10 17-Aug-01 2.38 0.00


Columns A, B, C, above with row numbers at the left.

I want to average each column for june-01 thru august-01 period

Then I want to average each column for Dec-00 thru August-01

More periods need to be averaged as needs dictate hopefully by changing a
"control" cell somewhere. That control cell should say "june-01 august-01",
etc or something equally meaningful. That way it's obvious what periods are
being included in the averages.

Actually, I have about 10 columns not just the two shown above. And each
column has almost 100 rows.

Thanks.

 

[Roger Govier]

Hi Don

I inserted 3 rows above your data, so 17-Nov-00 appears in cell A4
In A1 I entered 01/06/2001
In A2 I entered 31/08/2001

in cell B3 I array entered the following

{=AVERAGE(IF((TEXT($A$4:$A$13,"yymm")>=TEXT($A$1,"yymm"))*
(TEXT($A$4:$A$13,"yymm")<=TEXT($A$2,"yymm"))>0,B4:B13))}

To create an array formula, commit or Edit using Control + Shift + Enter
(CSE), not just Enter.
Excel will insert the curly braces { } when you use CSE. Do not type
them yourself.

Copy across through C3 etc.
Change ranges to suit or substitute named ranges

As you change dates in A1 and A2, so results will alter.
The above dates gave 2.76 for column B and 0.00 for column C
Changing dates to 1/12/2000 to 31/08/2001 gave results of 4.12 and 1.00
for columns B and C respectively.

These formulae use 0 values in the Average, provided the dates match.

 

[Don]

Excellent! Excellent! Excellent!

Thank you Roger for all your help.

Don

 

[Roger Govier]

Hi Don

Thank you for your response. You are more than welcome.
Glad that this has resolved your problem.