Results 1 to 3 of 3
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Excel 2003 String Formula

    I have a worksheet that keeps track of winnings in the Powerball lottery. I use it in a situation where a group of players buys 50 or more tickets for each drawing. The whole thing is very highly automated, thanks to extensive use of the capabilities of Excel. It is a truly useful application that has been developed over the past four years. There's one tiny loose end.

    The following formula returns a string showing how many winning tickets we have for a given drawing:

    =TRIM("Powerball results for "&TEXT(DatePA,"dddd m/d/yy")&": "&PAN1&","&PAN2&","&PAN3&","&PAN4&","&PAN5&","&PAN 6&". "&"We had "&COUNTIF(PAWinnerCount,"Winner!")&" "&"winning tickets for $"&SUM(PAWinnings)&".")

    A typical string that it returns is:

    Powerball results for Saturday 9/24/11: 3,4,12,27,44,26. We had 2 winning tickets for $10.

    The "tiny loose end" is that if there's only one winning ticket, the word "tickets" still shows up in the string, instead of the correct word "ticket". I'd like to fix this, but I'm not fluent in the use of IF and other conditional statements.

    Who can help me make it say "ticket" if we only have one winner?

    (BTW, the details of our lottery pool are HERE. I use the above-mentioned string to Tweet the results to the people who follow our progress.)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =TRIM("Powerball results for "&TEXT(datePA,"dddd m/d/yy")&": "&PAN1&","&PAN2&","&PAN3&","&PAN4&","&PAN5&","&PAN 6&". "&"We had "&COUNTIF(PAWinnerCount,"Winner!")&" winning ticket"&IF(COUNTIF(PAWinnerCount,"Winner!")=1,""," s")&" for $"&SUM(PAWinnings)&".")

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    Lou Sander (2011-10-14)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Great! It works like a charm.

    I've been wanting to fix this for many months, but the formula is so long and intricate, and my IF skills are so basic, that I was reluctant to tackle it. There are actually four flavors of the formula, all with different range names. It was a challenge to take your work and convert it to the other three flavors, but I did it.

    Leonardo daVinci is sometimes quoted as saying "He who can copy can do." As far as this stuff is concerned, he's right.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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