MAX(IF) complicated formula check

[Sheen]

Is this formula correct?

{=MAX(IF(OR(INDIRECT(ADDRESS(D1;24)):X144<0;INDIRECT(ADDRESS(D1;24)):X144>=0);Y23:Y144;""))}

Let's say D1=23. Column X is the column no. 24, hence 24 in the ADDRESS function.
It should take the range of X23:X144, check if each value in this range is smaller than, equal to, or greater than 0. If so - it should find the maximum corresponding value in the range Y23:Y144.
The essence of the problem is that I want to be able to change the value in D1, e.g. to 50 and that should change the relevant range to X50:X144.

[FJCC]

I am sorry that I don't understand what you are trying to do but I can point out that the parts of your formula that are like

Code: Select all

INDIRECT(ADDRESS(D1;24)):X144

need to be changed to

Code: Select all

INDIRECT(ADDRESS(D1;24) & ":X144")

It seems to me that your OR() will always be TRUE unless a cell contains an error. I tried a simplified version

Code: Select all

=OR(B6 < 0; B6 >= 0)

and it is TRUE if B6 contains a number or text or is blank.

[FJCC]

A simpler way to return a variable cell range is with the OFFSET function

Code: Select all

=OFFSET(X1; D1 - 1;0; 145 - D1;1)

That means: Start at X1, go down D1 - 1 rows, go right zero columns, expand the range to be 145 - D1 rows tall, make the range 1 column wide.

[Sheen]

I was trying to write a condition for "if cell does not contain "" ". In other words, the condition should only be true if there is a number in the cell. What formula to use for that? Is "" treated as a blank cell?

[robleyd]

This may help; from the offline help (F1)

ISBLANK
Returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty. A cell with a formula inside is not empty.
If an error occurs, the function returns a logical or numerical value.

[Villeroy]

Functions like Min, Max, Average, Sum, Count etc. ignore empty cells, empty strings and all kinds of text, even if the text represents a number. A blank cell, text "abc" or text "99" does not affect the result.

[Sheen]

I am stuck.
I have got:

SUMIF(X8:X144;">0")

D1 = 21
Then it may change to D1=23

All I want is X8 in the range to change to X21, or X23, or X(whatever_number_is_in_D1).

I am frustrated as no solutions suggested work. I get only errors. Please help.

[Lupp]

Code: Select all

=SUMIF(OFFSET($X$8;$D$21-8;0;144-$D$21+1;1);">0")
or
=SUMIF(INDIRECT("X" & $D$21 & ":X144");">0")

The function you finally apply to the range is not of actual meaning in the context.
This thread is about how to get cell ranges (always rectangular!) depending on variable values.

OFFSET produces a reference to a cell range of five parameters of which the first one is a reference itself giving the cell to which the row offset and the column offset refer, and the following four are numbers. Read the help for more detail.

INDIRECT creates the reference from the address passed to it which is a text ('String'). There is no sorcery! You need to compose the string exactly as if you typed it directly. ADDRESS can help you to get the addresses of pole cells. The range operator ":" must be inserted by concatenation. You can also concatenate the range address completely from whatever parts you want - under the condition, of course, that the syntax is correct, and that you regard the meaning you intend.