Alternative to Array formula in Excel Cells CTRL+Shift+Enter

[chappers86]

Good Morning,Afternoon,Evening,


I am comfortable with using array forumal in excel, however the peice of software i'm using that uses excel templates does not.

Does anyone have an anser to an alternative formula for the following:

=STDEV(if(group = grouprange, range))
CTRL+SHIFT+ENTER

I gather it is very simple but I'm looking for a STDEV if function as my original software that uses excel template support resets the array as if it was a normal formula.

Kind Regards

Chappers86

 

[Jonmo1]

Welcome to the board

Try

=STDEV(INDEX((group=grouprange)*range,0))

 

[chappers86]

Welcome to the board

Try

=STDEV(INDEX((group=grouprange)*range,0))

Thanks Jonmo1,

unfortunately it didn't work. I have a quick example and its results with the formula above:
[TABLE="width: 828"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]{=STDEV(if(group = grouprange, range))}[/TD]
[TD]=STDEV(INDEX((group=grouprange)*range,0))[/TD]
[TD]=STDEV(selectedrange)[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Value[/TD]
[TD]Array[/TD]
[TD]Index[/TD]
[TD]Normal[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]53[/TD]
[TD]30.27044763[/TD]
[TD]31.4164687[/TD]
[TD]30.27044763[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]70[/TD]
[TD]30.27044763[/TD]
[TD]31.4164687[/TD]
[TD]30.27044763[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]14[/TD]
[TD]30.27044763[/TD]
[TD]31.4164687[/TD]
[TD]30.27044763[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]37[/TD]
[TD]30.27044763[/TD]
[TD]31.4164687[/TD]
[TD]30.27044763[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]93[/TD]
[TD]30.27044763[/TD]
[TD]31.4164687[/TD]
[TD]30.27044763[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]33[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]35[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]97[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]53[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]25[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]78[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]87[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]10[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]10[/TD]
[TD]32.89419064[/TD]
[TD]35.00047095[/TD]
[TD]32.89419064[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Yes, because Jonmo's set-up will result in zeroes where the condition is not met.

Try:

=STDEV(IF(MMULT(0+(grouprange=group),1),range))

Regards

 

[chappers86]

Yes, because Jonmo's set-up will result in zeroes where the condition is not met.

Try:

=STDEV(IF(MMULT(0+(grouprange=group),1),range))

Regards

Works a treat! thank you for your help.

 

[XOR LX]

You're welcome!

Cheers