Problem with Indirect function in an Array Formula

[gassarma]

Hi, i am trying use Indirect function in an array formula.
="{="&INDIRECT("J1")&"(IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))}"
Where the cell J1 contains a cell dropdown with options such as product, average, min, max and the user can determine whether he wants an average or a product of the array forumla result. It is resulting in only the text. Can anyone please help.

Thanks

 

[Andrew Poulsom]

Remove the curly braces {} and use the FormulaArray property.

 

[gassarma]

Andrew, thanks for the response. However, my formula is included in a cell and not in a macro. I dont know how to use FormulaArray property in the formla. is it possible?

Thanks

 

[Andrew Poulsom]

Is this what you want?

=INDIRECT("J1")&IF(('Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761)

confirmed with Ctrl+Shift+Enter. Excel will add the curly braces {}.

 

[GlennUK]

Hi, i am trying use Indirect function in an array formula.
="{="&INDIRECT("J1")&"(IF((Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761))}"
Where the cell J1 contains a cell dropdown with options such as product, average, min, max and the user can determine whether he wants an average or a product of the array forumla result. It is resulting in only the text. Can anyone please help.

Thanks

You can't evaluate a string of a function like MIN, MAX, or AVERAGE using cell formulas. Have you considered writing your own custom function in VBA?

 

[Andrew Poulsom]

You can't evaluate a string of a function like MIN, MAX, or AVERAGE using cell formulas. Have you considered writing your own custom function in VBA?

I completely missed that requirement.

 

[GlennUK]

I completely missed that requirement.

I must admit I was surprised at that .... it's usually the other way round ( I answer a question, and you point out what the real requirement is )

 

[barry houdini]

One way to do this is use the old Excel4 macro EVALUATE

Define a named range called FlexFormula, using this in the "refers to" box

=EVALUATE('Sheet 1'!$J$1&"(IF(('Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!$M$2:$M$3761))")

Somewhere in the worksheet use this formula

=FlexFormula

and change J1 to the required function

[Note: when I tested the result updated if I changed the function in J1 but if I changed the data in the ranges it didn't update unless I re-entered the formula - I'm using Excel 2007, I don't know if it's peculiar to that version......]

 

[gassarma]

This formula yielded a result "Product 1.02". 1.02 being the result (only one) of the If condition. Where if condition more resulted in more than 1 data point, i am getting a '0'.

 

[gassarma]

Re: Problem with Indirect function in an Array Formula
<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>Is this what you want?

=INDIRECT("J1")&IF(('Sheet 1'!$B$2:$B$3761=$A9)*('Sheet 1'!$H$2:$H$3761=1)*('Sheet 1'!$J$2:$J$3761=1),'Sheet 1'!M$2:M$3761)

confirmed with Ctrl+Shift+Enter. Excel will add the curly braces {}.

This formula yielded a result "Product 1.02". 1.02 being the result (only one) of the If condition. Where if condition more resulted in more than 1 data point, i am getting a '0'