# Thread: Excel 2003 String Formula

1. ## 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.)

2. =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-13)

4. 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.

#### Posting Permissions

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