Hi all,
Sorry its been a while since I've posted. To make up for it I'm doing a Hangout On Air tomorrow about manipulating Form data. I'll post the video here afterwards. Enjoy!
Ted J
Tuesday, August 7, 2012
Wednesday, May 30, 2012
=sumproduct() follow-up with live stream!
Thanks to those who watched live. You should be able to see the recording below. Enjoy!
The above video is related to the shared spreadsheet below:
Vertical Summing Examples
Functions covered include =sumproduct(), =sumif(), and =query().
Cheers!
The above video is related to the shared spreadsheet below:
Vertical Summing Examples
Functions covered include =sumproduct(), =sumif(), and =query().
Cheers!
Saturday, May 19, 2012
Using =sumproduct() in Google Spreadsheets
It has been a while since I posted but I promise to have some exciting posts soon! But for today I thought I would show a quick example of a way to flip sumif behavior.
Consider a situation where all odd number rows have names and all even number rows have a numerical value in a single column. In my example, I either have Ted or Adam in the odd number rows. The goal here is to find the sum of the values directly below each time Ted appears and to find the sum of the values directly below each time Adam appears.
Formula in D1: =SUMPRODUCT(N(A$1:A$19=C1),A$2:A$20)+INDEX(0,1)
Formula in D2: =ARRAYFORMULA(SUMPRODUCT(N(A$1:A$19=C2),A$2:A$20))
What does sumproduct() do?
Sum product takes up to ??? (documentation says 30 but I think it is closer to 255) ranges (in Google Spreadsheets) or 255 ranges (in Excel) and multiplies the corresponding values together and then sums up all of the values. If my function was =sumproduct({2,3,4},{1,2,3}) the result would be 20 (2*1 + 3*2 + 4*3). In order for the sum and multiplication to work we have to turn our criteria into a number. Therefore we use the N() function to turn a value into 1 if the criteria is met or 0 if not. The second step is to define the range we want to multiply the first range by and that is basically the same range we had before but shifted down a cell. Instead of A1:A19 it becomes A2:A20.
To visualize what is going on see the tab called Visualize in the example or watch the video below.
Now that we have defined our ranges to be multiplied, the function is ready to go! Since SUMPRODUCT must be array enabled to work we must apply an array-enabler. I've illustrated two ways to do this depending on whether you are using Google Spreadsheets, Excel, or both. By wrapping the whole function with arrayformula() you enable the function to do array calculation. You can also do this by adding index(0,1) which when evaluated is always zero and has no effect on the function, but enables the array calculation for Google Spreadsheets and Excel. Arrayformula() is not supported by Excel and you will see a #NAME? error.
Enjoy and feel free to add any questions in the comments.
Also, see the video explanation recorded as a Google Hangout On Air and feel free to make a copy of the Spreadsheet to explore.
Cheers!
Consider a situation where all odd number rows have names and all even number rows have a numerical value in a single column. In my example, I either have Ted or Adam in the odd number rows. The goal here is to find the sum of the values directly below each time Ted appears and to find the sum of the values directly below each time Adam appears.
Formula in D1: =SUMPRODUCT(N(A$1:A$19=C1),A$2:A$20)+INDEX(0,1)
Formula in D2: =ARRAYFORMULA(SUMPRODUCT(N(A$1:A$19=C2),A$2:A$20))
What does sumproduct() do?
Sum product takes up to ??? (documentation says 30 but I think it is closer to 255) ranges (in Google Spreadsheets) or 255 ranges (in Excel) and multiplies the corresponding values together and then sums up all of the values. If my function was =sumproduct({2,3,4},{1,2,3}) the result would be 20 (2*1 + 3*2 + 4*3). In order for the sum and multiplication to work we have to turn our criteria into a number. Therefore we use the N() function to turn a value into 1 if the criteria is met or 0 if not. The second step is to define the range we want to multiply the first range by and that is basically the same range we had before but shifted down a cell. Instead of A1:A19 it becomes A2:A20.
To visualize what is going on see the tab called Visualize in the example or watch the video below.
Now that we have defined our ranges to be multiplied, the function is ready to go! Since SUMPRODUCT must be array enabled to work we must apply an array-enabler. I've illustrated two ways to do this depending on whether you are using Google Spreadsheets, Excel, or both. By wrapping the whole function with arrayformula() you enable the function to do array calculation. You can also do this by adding index(0,1) which when evaluated is always zero and has no effect on the function, but enables the array calculation for Google Spreadsheets and Excel. Arrayformula() is not supported by Excel and you will see a #NAME? error.
Enjoy and feel free to add any questions in the comments.
Also, see the video explanation recorded as a Google Hangout On Air and feel free to make a copy of the Spreadsheet to explore.
Cheers!
Saturday, March 24, 2012
Barack Obama, Alex Morgan, Ted Juch and March Madness
Well, we know who the Elite 8 are for the NCAA Men's College Basketball Tournament. How is your bracket doing? I'm sitting somewhere between Alex Morgan and Barack Obama.
I've seemed to iron out most of the kinks for using Gdocs to run your blog so I'm posting a small pool that I created below. There have been a few more feature requests but unfortunately I haven't gotten around to doing them. This just leaves room for more improvements for next year!
Enjoy!
I've seemed to iron out most of the kinks for using Gdocs to run your blog so I'm posting a small pool that I created below. There have been a few more feature requests but unfortunately I haven't gotten around to doing them. This just leaves room for more improvements for next year!
Enjoy!
Thursday, March 15, 2012
Using TedTournament() vs GoogleTournament()
Google updated their function yesterday and released a template today. I had nothing to do with the template that was released today but it was a copy of last year's with the year replaced as 2012. This proof is in 'Full Bracket'!C7 and any of the play-in games if you are curious. So if you want the updates you'll have to use my versions.
That being said, my templates should be functioning correctly. I've modified the back end so that Google's shortnames match up with Adam Bartlett's guesses at the shortnames. Therefore, assuming everything flows correctly on Google's end, I won't have to manually update my master spreadsheet. It will be updated as soon as GoogleTournament() gets updated.
Since this is a Docs blog I'll explain how I automated the back end. My master sheet before had empty cells where I would update the teams as they won. This means that if I am not near a computer or internet, you would have to wait and this really wasn't acceptable to me. Now, my mastersheet combines a vlookup, googletournament, and iferror. I created a table, which I will call "Helper" that lists Google's shortnames in column A and column B has Adam's names.
It looks something like =iferror(vlookup(googletournament(year,"men",round,slot,"statistic,team),Helper,2,FALSE))
This finds the shortname of the team using Google Tournament, matches it up with Adam's version of the shortname and returns Adam's version (which matches our template). If vlookup fails then it returns blank. The vlookup only fails with googletournament fails which indicates a game hasn't been played yet.
Anyways, I think my template is more robust that Google's template and easier to use, but the bugs are still being ironed out. Unfortunately, I can only update my template for any bugs that are found and not your own copies of my template. I'm trying to make any updates easily translatable.
Update:
There was an error in the point calculations, which I should have noticed from the beginning. There is an extra row in the full template vs the blank template. This means that my method of assigning points wasn't matching correctly. I've updated the Points sheet to calculate correctly. You can either make a new copy of my template or copy and paste D5:F67 from the points tab on my template to yours. You will have to unhide D:E and then rehide after you make the adjustments. The Single Bracket has also been updated. Replace G4 on the points tab with:
=if(F4="TBD",iferror(1/0),indirect("'"&G$1&"'!"&$E4&$D4-1)) <--Drag this all the way down to G66
and I4 with
=arrayformula(if(len(F4:F)>0,if(G4:G=F4:F,C4:C,0),0)) <--You don't have to drag this.
Next week I'll update the Google Sites template to show how all of this could be displayed through Google Sites. I'm planning on using 5 brackets in the pool along with Obama's. Should be fun to see how people match up!
Happy Madness!
The follow links are the same links from my original post:
That being said, my templates should be functioning correctly. I've modified the back end so that Google's shortnames match up with Adam Bartlett's guesses at the shortnames. Therefore, assuming everything flows correctly on Google's end, I won't have to manually update my master spreadsheet. It will be updated as soon as GoogleTournament() gets updated.
Since this is a Docs blog I'll explain how I automated the back end. My master sheet before had empty cells where I would update the teams as they won. This means that if I am not near a computer or internet, you would have to wait and this really wasn't acceptable to me. Now, my mastersheet combines a vlookup, googletournament, and iferror. I created a table, which I will call "Helper" that lists Google's shortnames in column A and column B has Adam's names.
It looks something like =iferror(vlookup(googletournament(year,"men",round,slot,"statistic,team),Helper,2,FALSE))
This finds the shortname of the team using Google Tournament, matches it up with Adam's version of the shortname and returns Adam's version (which matches our template). If vlookup fails then it returns blank. The vlookup only fails with googletournament fails which indicates a game hasn't been played yet.
Anyways, I think my template is more robust that Google's template and easier to use, but the bugs are still being ironed out. Unfortunately, I can only update my template for any bugs that are found and not your own copies of my template. I'm trying to make any updates easily translatable.
Update:
There was an error in the point calculations, which I should have noticed from the beginning. There is an extra row in the full template vs the blank template. This means that my method of assigning points wasn't matching correctly. I've updated the Points sheet to calculate correctly. You can either make a new copy of my template or copy and paste D5:F67 from the points tab on my template to yours. You will have to unhide D:E and then rehide after you make the adjustments. The Single Bracket has also been updated. Replace G4 on the points tab with:
=if(F4="TBD",iferror(1/0),indirect("'"&G$1&"'!"&$E4&$D4-1)) <--Drag this all the way down to G66
and I4 with
=arrayformula(if(len(F4:F)>0,if(G4:G=F4:F,C4:C,0),0)) <--You don't have to drag this.
Next week I'll update the Google Sites template to show how all of this could be displayed through Google Sites. I'm planning on using 5 brackets in the pool along with Obama's. Should be fun to see how people match up!
Happy Madness!
The follow links are the same links from my original post:
Subscribe to:
Posts (Atom)