Google Spreadsheet Post #1886
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jan-25-2015
post by Chris Bromige:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/47j_HXpULGE
Updating 2nd sheet's data using "form response" data
Yogi,
--------------------------------------------------------------
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/47j_HXpULGE
Updating 2nd sheet's data using "form response" data
Yogi,
Your suggestion regarding utilization of ArrayFormula to automatically fill rows works a charm for columns A and E of "Sorted", and the correct name of the winning and losing teams are appearing there.
However, applying the same ArrayFormula to columns B and D, where I'm hoping to have EACH row of data scanned for the winning team's score, and haviing that displayed in column B and having the losing team's score displayed in column D, resulted in sheets finding the highest/lowest score in ANY row and filling the column with that score.
Something that is complicating the entire project is my desire to allow for the possibility that unexpected teams may beat ranked teams.
so, given this data from form responses:
Column B C D E F G H I J K L
| Team Name | Date of GAME | Your (Top 25ish) team's score? | Top 25 ish opponent? | Top 25ish opponent's score | Any note you wish to add about this game: | "newcomer" school name: | Date of GAME | newcomer team's score? | Top 25 ish opponent? | Top 25ish team's score? |
| Oak Bay | 1/19/2015 | 59 | SMU | 56 | ||||||
| newcomer | St. Patrick's | 1/19/2015 | 75 | Saint George's | 72 | |||||
| Byng | 1/17/2015 | 51 | Kitsilano | 54 | ||||||
| Byng | 1/17/2015 | 52 | Kelowna | 51 |
I am hoping for this output:
Column A B C D E
| Winner | Pts. | Loser | Pts. | Game Date |
| Oak Bay | 59 | SMU | 56 | 1/19/2015 |
| St. Patrick's | 75 | Saint George's | 72 | 1/19/2015 |
| Kitsilano | 54 | Byng | 51 | 1/17/2015 |
| Byng | 52 | Kelowna | 51 | 1/17/2015 |
So, with Yogi's help, I've got ArrayFormulas working to get me the correct winning and losing team names, and correct game date. It is the points scored by the winner and the loser that are now giving me difficulty.
The following formula gets me the correct points in B2, but I can't make it work as an ArrayFormula
=if(Resp!B2="newcomer", Max(Resp!J2,Resp!L2), MAX(Resp!D2,Resp!F2))
This formula references the form response sheet ("Resp") and initially checks whether an unranked, unexpected team has appeared ("newcomer"), if it has, the formula then looks for the larger score in cells J2 and L2. If the game was one between two ranked teams, it looks for the highest score in D2 or F2. When I tried using the same arrayformula format as Yogi suggested for the team name columns, things went sideways:
=ArrayFormula(if(Resp!B2:B="
Just resulted in the formula looking for the highest score, irrespective of the row it appeared in. All rows on my "sorted" sheet displayed the highest score any team achieved in the entire array:
| Winner | Pts. | Loser | Pts. | Current Rank (winner) | Current Rank (loser) |
| Oak Bay | 90 | SMU | 30 | ||
| St. Patrick's | 75 | Saint George's | 30 | ||
| Kitsilano | 90 | Byng | 30 | ||
| Byng | 90 | Kelowna | 30 | ||
| Kitsilano | 90 | Byng | 30 |
No comments:
Post a Comment