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:

		{=STDEV(if(group = grouprange, range))}	=STDEV(INDEX((group=grouprange)*range,0))	=STDEV(selectedrange)
Cat	Value	Array	Index	Normal
a	53	30.27044763	31.4164687	30.27044763
a	70	30.27044763	31.4164687	30.27044763
a	14	30.27044763	31.4164687	30.27044763
a	37	30.27044763	31.4164687	30.27044763
a	93	30.27044763	31.4164687	30.27044763
b	33	32.89419064	35.00047095	32.89419064
b	35	32.89419064	35.00047095	32.89419064
b	97	32.89419064	35.00047095	32.89419064
b	53	32.89419064	35.00047095	32.89419064
b	25	32.89419064	35.00047095	32.89419064
b	78	32.89419064	35.00047095	32.89419064
b	87	32.89419064	35.00047095	32.89419064
b	10	32.89419064	35.00047095	32.89419064
b	10	32.89419064	35.00047095	32.89419064

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

 

[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