More Selective Maximums

[dranon]

OK, not so perfect.

I need to expand the versatility of the basic equation. The basic
equation is:

{=MAX(IF($U1:U1>1000,$A1:A1,0))}

The actual ranges ($U1:U1 and $A1:A1 in the above example) are being
determined using OFFSET AND INDIRECT.

If I try, as an example:

{=MAX(IF(OFFSET($U1,0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

It works just fine.

So I continue with my programming efforts and proceed to putting "U1"
in cell Z1 and try:

{=MAX(IF(OFFSET(INDIRECT(Z1),0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

And that still works.

So, with one last programming change, I change the value of Z1 from
"U1" to just "U" and try:

{=MAX(IF(OFFSET(INDIRECT(Z1&ROW()),0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

and now I get a #VALUE (Yes, I entered it as an array formula using
cntl-shift-enter).

If I just take out the IF statement but leave the MAX, while expanding
the array (to six columns), the array formula works just fine using
the ROW() construct:

{=MAX(OFFSET(INDIRECT(Z1&ROW()),0,0,1,6)}

So it looks like the OFFSET function can take an INDIRECT function's
results as its first argument in some functions (MAX, SUM) but not in
the IF.

If you haven't guessed by now, I have a "Specifications" page that
defines the starting column for the averages I'm running. I then use
something like COLUMN()+1-COLUMN(INDIRECT(SPECS!$D$1&ROW())) to
determine with width of the array to process. This works, as
indicated above, on basic SUM and MAX formulas. It is only when
combined as above that I get the error.

By using the Specifications page I end up with a worksheet where it is
trivial to add another column, because all of the array indexes
automatically readjust themselves using the noted formulas.

So I have two questions:

1) Am I correct that the combination I've identified just won't work?

2) If so, is there a workaround.

Thanks

==========================
Perfect. Thanks.

 

[dranon]

Found the answer with a google search. I need to change:

{=MAX(IF(OFFSET(INDIRECT(Z1&ROW()),0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

to:

{=MAX(IF(OFFSET(INDIRECT(Z1&SUM(ROW())),0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

Excel is amazing.

 

[Roger Govier]

Hi

You could make life much easier for yourself and avoid both of the volatile
Indirect and Offset functions by creating 2 dynamic named ranges.

Using Z1 to hold the first row value that you want to test, and Z2 to hold
the second row value, then create 2 named ranges
Insert>Name>Define>
Name Range1
Refers to =INDEX($U:$U,$Z$1):INDEX($U:$U,$Z$2)

Name Range2
Refers to =INDEX($A:$A,$Z$1):INDEX($A:$A,$Z$2)

Your formula then simplifies to
{=MAX(IF(Range1>0,Range2,0))}

--

Regards
Roger Govier

Found the answer with a google search. I need to change:

{=MAX(IF(OFFSET(INDIRECT(Z1&ROW()),0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

to:

{=MAX(IF(OFFSET(INDIRECT(Z1&SUM(ROW())),0,0,1,1)>1000,OFFSET($A1,0,0,1,1),0))}

Excel is amazing.





__________ Information from ESET Smart Security, version of virus
signature database 5526 (20101013) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5526 (20101013) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[dranon]

Maybe I'm not understanding what your formula does, but my formulas
operate on a single row. It looks like you involve row 1 and row 2 in
a single calculation. That won't work as I need to restrict my
calculations to a single row.

Or am I misunderstanding how your formula works?

Thanks for chiming in, one way or the other.