Go to Post It doesn't take money, just desire. - BillP [more]
Home
Go Back   Chief Delphi > Technical > Technical Discussion
CD-Media   CD-Spy  
portal register members calendar search Today's Posts Mark Forums Read FAQ rules

 
 
 
Thread Tools Rate Thread Display Modes
Prev Previous Post   Next Post Next
  #10   Spotlight this post!  
Unread 06-08-2014, 11:05
Jon Stratis's Avatar
Jon Stratis Jon Stratis is offline
Mentor, LRI, MN RPC
FRC #2177 (The Robettes)
Team Role: Mentor
 
Join Date: Feb 2007
Rookie Year: 2006
Location: Minnesota
Posts: 3,827
Jon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond reputeJon Stratis has a reputation beyond repute
Re: How to CALCULATE assist points

Perhaps a better way of doing the calculation:

Quote:
=VLOOKUP(MIN(IF(COUNTA(C4:C6)>0,1,0)+IF(COUNTA(D4: D6)>0,1,0)+IF(COUNTA(E4:E6)>0,1,0), IF(COUNTA(C4:E4)>0,1,0)+IF(COUNTA(C5:E5)>0,1,0)+IF (COUNTA(C6:E6)>0,1,0)),G3:H6,2)
In this, I included the counta functions, although you could certainly utilize the ones to the side and bottom of the table you mentioned, which makes this equation even easier:

Quote:
=VLOOKUP(MIN(COUNTIF(C7:E7, ">0"), COUNTIF(F4:F6, ">0")),G3:H6,2)
Basically, we want to know how many rows and how many columns have something in them. From there, we could look at each possible combination exhaustively... but it's easier to look at the minimum of the two, as that will be the number of credited assists. Then I just created another table G3:H6 that has 0-4 in the first column and the corresponding scores in the second (0,0,10,30). Doing a vlookup on that second table lets me find the score that matches the number of assists performed and return it.

Note: Why doesn't excel have a function like Oracle's DECODE function in SQL? I so wanted to use that for this solution!

Edit: I just realized there's an edge case this doesn't handle correctly - two robots both poses in zone 1, then a third possesses in zones 2 and 3. Back to the drawing board!
__________________
2007 - Present: Mentor, 2177 The Robettes
LRI: North Star 2012-2016; Lake Superior 2013-2014; MN State Tournament 2013-2014, 2016; Galileo 2016; Iowa 2017
2015: North Star Regional Volunteer of the Year
2016: Lake Superior WFFA

Last edited by Jon Stratis : 06-08-2014 at 11:14.
 


Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -5. The time now is 01:57.

The Chief Delphi Forums are sponsored by Innovation First International, Inc.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright © Chief Delphi