STDEV.P IF cells in the column contain specific text "*cta*"

user_8356237 0
Viewed 65

I would like to perform STDEV.P calculation based on criteria in one column.

My current formula is:

{=STDEV.P(IF(Aggregated!Y$15:Y$3000="*cta*",Aggregated!AD15:AD3000,""))}

E.g. the case of AVERAGE(IF... is much simpler cos I do not use "=" but instead have "*cta*" as one of the criteria.

The result is #DIV/0!

PS. I face a similar issue with MAX(IF(... where the criteria is "*cta*"

Please advise. Thank you

excel-formula excel
1 Answers

Try this

=STDEV.P(IF(ISNUMBER(SEARCH("cta",Aggregated!Y$15:Y$3000)),Aggregated!AD15:AD3000))

and for MAX:

=MAX(IF(ISNUMBER(SEARCH("cta",Aggregated!Y$15:Y$3000)),Aggregated!AD15:AD3000))

user_92452450
Related