Results 1 to 3 of 3

Need Complex Formula Help for Volleyball Rotation

This is a discussion on Need Complex Formula Help for Volleyball Rotation within the Excel Questions forums, part of the Question Forums category; I am creating a volleyball rotation chart. I have the lineup in B7:B16 for this example. If you study Rotation ...

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Posts
    98

    Default Need Complex Formula Help for Volleyball Rotation

    I am creating a volleyball rotation chart. I have the lineup in B7:B16 for this example. If you study Rotation 1-3, you will see that from Rotation 1 to Rotation 2, Name 1 leaves the rotation, Name 7 comes in at location 6, location 6 goes to 5, 5 to 4, 4 to 3, 3 to 2 and 2 to 1. Note that Name 1 does not leave the game but goes to the bottom of the rotation.

    I am only showing three Rotation panels but there are actually 15 total which allows me to have 15 players in the lineup and show all 15 rotations.

    So here is what I need... I want a formula in F13, F9, E9, D9, D13 and E13 that will rotate the lineup through as noted. But here is the kicker, if one team has 9 players another has 15 and another has 12, the formula should adjust the number of players and leave the unused rotations blank - i.e., if there are 10 players then only Rotation 1-10 will be used and 11-15 will be blank. If there are 9 players, then Rotation 1-9 will be used and 10-15 will be blank.

    For now I have brute forced it but this will only work for a lineup of 10 players. I am thinking this will require the use of index and offsets, I just can't figure it out after hours of attempts.

    Thanks for the help!!!!!

    ******** ******************** ************************************************************************>
    Microsoft Excel - 2009 Fall Volleyball.xls___Running: 11.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    M
    N
    O
    P
    6
    Input Lineup Here  Rotation 1    Rotation 2    Rotation 3  
    7
    1Name 1 432  432  432
    8
    2Name 2              
    9
    3Name 3 Name 4Name 3Name 2  Name 5Name 4Name 3  Name 6Name 5Name 4
    10
    4Name 4              
    11
    5Name 5 561  561  561
    12
    6Name 6              
    13
    7Name 7 Name 5Name 6Name 1  Name 6Name 7Name 2  Name 7Name 8Name 3
    14
    8Name 8              
    15
    9Name 9              
    16
    10Name 10              
    Code 

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  2. #2
    Board Regular
    Join Date
    Sep 2009
    Posts
    111

    Default Re: Need Complex Formula Help for Volleyball Rotation

    Hi

    I have re-arranged the rotations for easy formula copying. The formulae in rows 9 to 12 are copied from row 8.




    ABCDEFGH
    6 Rotation 1 Rotation 2
    71Name 1 4Name 4 3Name 3
    82Name 2 3Name 3 2Name 2
    93Name 3 2Name 2 1Name 1
    104Name 4 1Name 1 10Name 10
    115Name 5 10Name 10 9Name 9
    126Name 6 9Name 9 8Name 8
    137Name 7
    148Name 8
    159Name 9
    1610Name 10



    Spreadsheet Formulas
    CellFormula
    E7=INDEX($B$7:$B$21,D7)
    G7=IF(D7-1=0,MAX($A$7:$A$21),D7-1)
    H7=INDEX($B$7:$B$21,G7)
    D8=IF(D7-1=0,MAX($A$7:$A$21),D7-1)
    E8=INDEX($B$7:$B$21,D8)
    G8=IF(D8-1=0,MAX($A$7:$A$21),D8-1)
    H8=INDEX($B$7:$B$21,G8)



    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


    If you have fewer players the speadsheet looks like this:


    ABCDEFGH
    6 Rotation 1 Rotation 2
    71Name 1 4Name 4 3Name 3
    82Name 2 3Name 3 2Name 2
    93Name 3 2Name 2 1Name 1
    104Name 4 1Name 1 8Name 8
    115Name 5 8Name 8 7Name 7
    126Name 6 7Name 7 6Name 6
    137Name 7
    148Name 8
    15
    16

  3. #3
    New Member
    Join Date
    Oct 2009
    Posts
    1

    Default Re: Need Complex Formula Help for Volleyball Rotation

    Hi - is there somewhere that I can download the xls file for this volleyball rotation?

    This would definitely be useful!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com