Friday, October 11, 2013

yogi_Compute Row By Row Average Of Numeric Values In Range D3:H11

                                          Google Spreadsheet   Post  #1394
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Oct 11, 2013
question by: Philip Duker (http://productforums.google.com/forum/?zx=pod4a6fmf695#!mydiscussions/docs/kbj1Gr4G2sc)
2nd part of the question in the thread
Hi Yogi, and forum
Thanks so much for your work on this problem - you've come up with a pretty great solution that is much simpler than the script writing that I was doing.  The only problem I'm having now, is that I want to try to take the average of the number scores (while ignoring the letter grades), and everything has been converted to Text instead of Numeric form.  I saw that you had a blog post on dealing with this issue:

http://yogi--anand-consulting.blogspot.com/2013/05/yogicoerce-numerics-as-text-into-true.html

So I was trying to adapt your formulas to convert the numbers back into true numbers, using this formula:
=ArrayFormula(if(D3="","",Average(value(D3:H3))))
I don't understand the logic behind the formula, so it's unsurprising that I'm not getting it work: it can't deal with text and returns the error "Cannot Parse text: P"

I know that you can covert a number back to it's true numeric form with Value(), but is there a way for this to avoid the actual text as well.  I've been messing around with some If() statements and using Filter, but I haven't been able to come up with something that will work.
Any help would be greatly appreciated.

Cheers,
Phil

---
OK, I think I might have figured this one out.  The following formula works for what I'm after:
=Average(Filter(Arrayformula(value(E3:I3)), E3:I3<>"", isnumber(value(E3:I3))))
There might be a simpler way to do it; but this works for me.
Any comments or suggestions for improvement are welcome, but I think this should work for now.
Big thanks again to Yogi for his help.


Cheers,
Phil

-----------------------------------------------------------------------------------------------------------------------------------------------------

No comments:

Post a Comment