Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    663
    Thanks
    88
    Thanked 7 Times in 6 Posts

    Unusual Table Lookup

    The attached Vital Statistics table is part of a worksheet in a many-paged workbook. It keeps track of lottery drawings during a three-month period. Here are some characteristics of the drawings it keeps track of:

    • The drawings are always on Tue, Wed, Fri, and Sat.
    • Depending on the calendar, the first drawing in a period can be on any one of these days.
    • Also depending on the calendar, there can be different numbers of PowerBall (PB) and Mega Millions (MM) drawings during the three-month period.

    The workbook is set up so that the cells in the attached table are automatically filled in each time a new period starts.

    As soon as the day of a drawing ends, conditional formatting grays out that day's entry in the table. I need one thing more:

    When the day of a drawing ends, I want Excel to plug the value of "Minimum Winnings to Go" from the NEXT drawing into cell M10.

    I've fooled around with this for a while, but my knowledge doesn't seem up to the task. Who can help?
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,762
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Hay Lou,

    Enter the formula in M10:

    Code:
    =INDEX(B4:J55,MATCH(MAX(IF(D4:D55<TODAY(),D4:D55,"")),D4:D55,0),6)
    This is an array formula so you will have to use Ctrl-Shift_Enter after pasting into cell (Note the curly brackets in the image). It will show the latest Winnings to go prior to today at 00:00.

    HTH,
    Maud

    LouS1.png
    Attached Files Attached Files

  3. #3
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,762
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Lou,

    I may have misunderstood you. If you want the NEXT drawing then use this array formula instead in M10:
    Code:
    =INDEX(B4:J55,MATCH(MIN(IF(D4:D55>=TODAY(),D4:D55,"")),D4:D55,0),6)
    Maud


    PS. Don't forget to use Ctrl-Shift-Enter

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2015-05-22)

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,078
    Thanks
    2
    Thanked 129 Times in 122 Posts
    If the data is sorted in date order as it appears to be in the sample, then you can use a regular formula:
    =INDEX(G4:G55,MATCH(TODAY(),D4:D55,1)+1)
    Last edited by RetiredGeek; 2015-05-22 at 05:14. Reason: Added noparse tags
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #5
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,762
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Rory,

    Yes, forgot about the match_type of 1 in the match formula.

  7. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    663
    Thanks
    88
    Thanked 7 Times in 6 Posts
    Every time I think I know something about Excel, the depth of my ignorance is revealed by something like this!

    The dates ARE always sorted from low to high.

    Maudibe's Comment #3 seems to work right now. (On the morning of Fri 05/22/15, the Minimum Winnings To Go is $80.00, which is the value appearing in M10 if I use that formula.) There is a drawing tonight, so we'll see what happens at midnight!

    rory's formula currently gives $77.00, which is the next Minimum Winnings To Go after the one I want. (On Friday morning, it gives Saturday's entry. Though it's Friday, the drawing isn't until the end of the day, so Friday's MWTG is good through the end of the day.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,078
    Thanks
    2
    Thanked 129 Times in 122 Posts
    If you want to match today, remove the +1 at the end of mine.
    Regards,
    Rory
    Microsoft MVP - Excel.

  9. The Following User Says Thank You to rory For This Useful Post:

    Lou Sander (2015-05-22)

  10. #8
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    663
    Thanks
    88
    Thanked 7 Times in 6 Posts
    That makes it succeed for today. We'll see what happens at midnight, when today's drawing is no longer the next one.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    663
    Thanks
    88
    Thanked 7 Times in 6 Posts
    After action report: Both versions successfully handled the transition from Friday to Saturday. We'll see what happens over the next few days.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #10
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,762
    Thanks
    74
    Thanked 378 Times in 346 Posts
    Thanks for the follow-up Lou

  13. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    663
    Thanks
    88
    Thanked 7 Times in 6 Posts
    More followup: Maudibe's formula successfully transitioned from Saturday to Sunday, putting Tuesday's $73.00 MinWinningsToGo into the box. The other one failed, leaving Saturday's $77.00 there.

    I'll keep observing this through a full week. I'll also start figuring out how the INDEX function works. (Also array formulas and all the other stuff.)
    Last edited by Lou Sander; 2015-05-24 at 09:20.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  14. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    1,991
    Thanks
    83
    Thanked 279 Times in 270 Posts
    Hi Lou

    ..I think Maud should get a share of the winnings then.

    zeddy

  15. #13
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    663
    Thanks
    88
    Thanked 7 Times in 6 Posts
    GMTA! Pool participants contribute $25 each quarter, in return for a share in any jackpot we win. We spend all the contributions on tickets. Many loungers have contributed to our very-highly-developed Excel workbook, which keeps track of participants, contributions, numbers played, bank balances, winnings, etc.

    We select our tickets so we are assured of winning something in every drawing. Typically we spend $3,000 on tickets each quarter, and win $250 or so. (It goes without saying that the $250 is unimportant -- we are looking to win a multimillion dollar jackpot.)

    The $250 goes toward expenses and a "kitty" to buy extra tickets when the jackpot is over $100 million.

    You can see the whole story on our website at www.ussrankin.org/lottery

    I promise the person who sells us the tickets that we will give them the car of their choice if they sell us a jackpot winner. (No million dollar sports cars -- a $130,000 Mercedes would be the max.)

    Depending on Maud's preference, I could offer him a share in the next pool (big potential payoff, but very little chance of seeing it), or maybe buy him a little something out of the kitty (minimal dollar value, but 100% chance of getting it).
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  16. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    1,991
    Thanks
    83
    Thanked 279 Times in 270 Posts
    Hi Lou

    ..just give him an extra thank you click then.

    We will miss your posts here if you get that $100million ticket.

    zeddy

  17. #15
    Silver Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,762
    Thanks
    74
    Thanked 378 Times in 346 Posts
    I'll reinvest my share into Lou's purchasing of his own Blog site.

    Lous secrets.png

Page 1 of 2 12 LastLast

Posting Permissions

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