Hello everyone, I need to perform some calculations on excel, however, I need to perform them with cells that have text characters in them.

ex)     B1: < 0.003, B2: < 0.004, B3: < 0.005

I need to find st.dev for these.

Can anyone help me with this? I am currently using this equation:

=STDEV(SUMPRODUCT(MID(0&B1,LARGE(INDEX(ISNUMBER(--MID(B1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10), SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(--MID(B2,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10), SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10))

But it doesn't give the right answer. The answer should be 0.001 but I am only getting 1. Can anyone help me fix this?

Thank you!

Alex

 

Re:  StdDev from mixed cells

  =STDEV(RIGHT(B1,5),RIGHT(B2,5),RIGHT(B3,5))

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

Re:  StdDev from mixed cells

  =STDEV(RIGHT(B1,5),RIGHT(B2,5),RIGHT(B3,5))

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

• Proposed as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 5 hours 18 minutes ago

Re:  StdDev from mixed cells

  =STDEV(RIGHT(B1,5),RIGHT(B2,5),RIGHT(B3,5))

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2
-or-
http://jmp.sh/K95N3ee

• Proposed as answer by George.Zhao CHNMicrosoft contingent staff, Moderator Monday, June 08, 2015 2:11 AM
• Marked as answer by aescobar17 13 hours 43 minutes ago

Thank you very much for your help!