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