INDIRECT within another formula?

[Sheen]

I am looking for a way to enter the number part of a cell reference in DMAX function by referring to another cell's value, e.g:
DMAX(X2:Y144;A1:B3)
I want to replace "2" in "X2" by the value of cell D1 which is 50. INDIRECT does not work within DMAX formula. Any other solutions?

[Villeroy]

of course INDIRECT works with DMAX like with any other function. It converts a string to a reference, in this case the concatenation of a literal "X" with the value in D1 and literal ":Y144"

="X" & D1 & ":Y144" with 13 in D1 yields X13:Y144 which is a perfectly valid cell address.

=DMAX(INDIRECT("X"&D1&":Y144");A1:B3)

[Sheen]

You are right. The problem lies in the fact that the changed range for DMAX or DAVERAGE does not include the table header - is there a way around it? Basically, I have some values in columns X and Y and I want to find the average of only those Y's for which the corresponding X is >0. So:
X1 -35 Y1 10
X2 -50 Y2 20
X3 -20 Y3 35
X4 10 Y4 15
X5 20 Y5 20

I would like to average only Y4 and Y5 in the range X1:Y5, as only for Y4 and Y5 the corresponding X4 and X5 are >0.

[Villeroy]

=DAVERAGE(X1:Y144;Y1;A1:A2)
Y1 is the column header of the values you want to calculate the average from
A1:A2 is a range where you store the criteria as described in the F1-help

Alternatively:
=DAVERAGE(X1:Y144; 2 ;A1:A2)
where 2 refers to the second column

Criteria range A1:A2
A1: =X1 [the column header in X1]
A2: >0

Criteria "X >0 up to 20":
A1: =X1
A2: >0
B1: =X1
B2: <=20

[Sheen]

The problem is that when I change the range from X1:Y144 to X10:Y144 then Y1 (the column header) is no longer included in this range and I get error 504

[Villeroy]

You don't need any INDIRECT to calculate the average of values that are selected by a criteria range. This is what DAVERAGE can do by its own.

[Sheen]

Yes, but does DAVERAGE require the range to include the column header?

[robleyd]

From the offline help (F1)

DAVERAGE
DAVERAGE returns the average of the values of all cells (fields) in all rows (database records) that match the specified search criteria.
The search supports regular expressions. You can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must precede every character with a \ character. You can switch the automatic evaluation of regular expression on and off in Tools - Options - OpenOffice Calc - Calculate.
Syntax
DAVERAGE(Database; DatabaseField; SearchCriteria)
To reference a column by means of the column header name, place quotation marks around the header name.

[Villeroy]

DAVERAGE does not need any offset to aggregate all Y where X>0. Just use the fill list including the column headers.

Example file of yesterdays other topic: download/file.php?id=32162 with results in F6:F9, criteria in G1:H2. Column G shows the same results from an array formula, the filtered pivot table does the same and the subtotal function too after you applied a filter to the source range. The source is a range of unordered random data, 1000 times and 1000 integer numbers. We aggregate numbers where the time is between 9am and 12.

If you really want to aggregate all Y where X>0 below a certain, you should add a row number (function row does that) and add the row number to the criteria range and to your source list. function ROW() returns the row number
Criteria range for X>0 and Row number R>99
X R
>0 >99