IF statement that differentiates between blank and zero

Hi All,
I am calculating sales of lottery tickets. Each ticket has a ticket number, and when our store manager enters the start and end ticket numbers for the day, Excel should show the $ amount sold. It's easy enough with New York's Lotto, because a book of 100 tickets starts at number 100 and ends at number 1, so 0 tickets means there are zero left. In Massachusetts a book of 100 tickets starts at 99 and the last ticket is #0. (seems stupid, but maybe there's a reason)

My issue is that the formula that I've come up with so far to calculate the $ sold is: =IF(ISTEXT(B5),(A5+1)*A$2,IF(B5>0,(A5-B5)*A$2,A5*A$2)) If the start ticket # is 10 and the end ticket # is 5, then 5 were sold and at $5 a ticket the total is $25. If the end ticket is 0, then one ticket is left and 10 were sold, so the $ sold is $50. If all of the tickets were sold I will need them to enter some sort of text in the cell, since 0 doesn't mean 0 are left. That's why I'm checking for text.

My spreadsheet is very large, with lots of blank cells. The formula works ok, except that if I change the formula to check if the cell=0, then it calculates the blank cells as if they were zero, even though they are actually blank, so for blanks it calculates as if there is one ticket left.

One solution would be to have the manager enter -1 when they are all sold, which I assume would work, but it's not a very elegant solution, since -1 doesn't really make any sense. I assume I could use VBA instead of a formula to check if there is any text in the cell, but I'm wondering if there is a non-vba solution.

Here is the results with the current formula: (see the file attached)

Ticket Value
$5.00

Start Ticket # End Ticket # $ Sold
10 5 $25.00
10 0 $50.00
10 x $55.00
10 4 $30.00
10 8 $10.00
10 $50.00

Let us see your formula.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Paste in C5 and then fill down

Formula:

Please Login or Register  to view this content.

Use 0 when outside Mass otherwise use a blank