the use of indirect in array or sumproduct formulas

Hi,

I am wondering what my options are regarding sumproduct and array formulas.

i know i can use indirect in these formulas like:
=sumproduct(--(indirect(A2)<>1)*(indirect(b2)<>1))
where A2 might be a named range...

but am i able to capture more with an indirect?

What I would like to be able to is have an indirect reference a cell that has all of my parameters in it...

e.g., =sumproduct(indirect(G1))
where G1 = --(indirect(A2)<>1)*(indirect(b2)<>1)

The reason i want to do this is, because i want to be able to construct the parameters of an array or sumproduct without going into each formula to edit it.

Does this make sense?

Josh

Hi,

Maybe I'm misunderstanding something, but if you have incorporated the range name in your sumproduct formula, why can't you just change the address of the range name which will adjust your formula result automatically.

HTH

I have attached an example worksheet to attempt to demonstrate what it is I am trying to accomplish. And perhaps I am misunderstanding your suggestion.

I am not picky, i am looking for any way (preferably the easiest way) to accomplish this task.

I'm open to whatever suggestions you may have.

Josh

fom your sheet i take it you are using named ranges?
=SUMPRODUCT((VAR01=4)*(VAR03<>1)) in b2 returns 3
how would you be expecting that to be used in for example
=sumproduct((indirect(B2))*var07)

Yes, you can assume the ranges are named (even though i did not actually name them in the workbook).

correct
=SUMPRODUCT((VAR01=4)*(VAR03<>1)) in b2 returns 3

so if B2 were to = (VAR01=4)*(VAR03<>1)

I would want:
=sumproduct((indirect(B2))*VAR07) to function as if it were:

=sumproduct((VAR01=4)*(VAR03<>1)*VAR07)

if i were to change B2, then that change would then be applied to the sumproduct.

what result would you expect from that?

I guess in this case i would expect the result of 7.

why i would want to compute that particular sumproduct is kind of arbitrary. The real issue is: can i make a part of the sumproduct refer to the contents of another cell.



The reason i want to be able to do this, is because i want to run calculations (whether it be an average, stdev, min, max, correl) based on certain parameters. However, i want to be able to change these parameters on a whim. I would prefer that change to occur in a single cell rather than having to change every formula that relates to the data.

Is this making any sense?

Ya, technically i think that would do the trick, but i also agree that it could result in other headaches.

Thank you for the idea.