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   Oct 11, 2013
question by: Philip Duker (!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:

So I was trying to adapt your formulas to convert the numbers back into true numbers, using this formula:
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.


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.