# Posts by darkyam

Re: Solver - To Set Ticket Prices

I'm a little confused why you feel you can't use formulas. If you know the number of member and non-member tickets, then the formula is =expenses/(<lower of 200 or # of member tickets>*.75+200-that number of tickets). This will get you the price of a full ticket. 75% of this number would, of course, be the cost of a member ticket.
For example, if expenses of \$5000 are entered in A1, and 150 member tickets are available (with this number in B1), then =A1/(B1*.75+200-B1) gets you \$30.77 per non-member ticket. 30.77*.75*150+30.77*50=\$5000.

Re: Solver - To Set Ticket Prices

If total expenses are in A1, =A1/200/.75. This will get you the lowest full price of a ticket you can have and still break even, even if you sell only 200 tickets and all are to members.

Re: Extract A Text After A Specific Keyword

I assumed the column of data you had is in column A and the formula I suggested was in column B. The cell would be blank if REFDES is not found, but would have R3099 or U079, etc., if it was. Bill's formula also works, but requires you to take the same steps after you've used it to get the data into one row.

For both of our formulas, you will have to drag the formula down the length of the array. Highlight the cell, move the mouse to the lower right-hand corner of it until it becomes a black cross, then click, hold, and drag down as far as necessary.

Re: Extract A Text After A Specific Keyword

Don't know the macro way, but you could do =IF(ISERROR(FIND(A1,"REFDES")),"",Right(A1,Len(A1)-FIND(A1,"="))). That will kick out the value you need in B. After that, you could use filters to not show blanks in B, highlight it, click Ctrl+;, copy and Paste Special (selecting Transpose and Values) where you need the info to be.

Re: Range To Increase Based On Critiera

Not sure how to improve the formula itself (aside from removing the unnecessary + at the beginning), but you could just highlight it, Ctrl+H, F in the first box, I in the second, and click Replace. You would just do this every month.

Re: After The Decimal Point

For Sicarii's method, I would suggest putting +0 or *1 at the end to convert it to a number; otherwise, Excel reads it as text and formulas won't work.

Re: After The Decimal Point

=Mod(A1,1). This doesn't work for negative numbers, though.

Re: Zip Code To Zip Code Data - Best Way?

I tried to do this with an Index/Match combination, but couldn't get it to work quite right. The formula in the attachment works for me, though.

## Files

Re: Removing Trailing Commas

Did the formula I give you not work? You could add another IF loop if there are cells that don't end with a comma, but I think what I put in should do the job for you if they all end in at least one comma.

Re: Biggest Loser

You just select the cell and format as a %. In 2007, the button is on the Home tab in the Number group.

Re: Removing Trailing Commas

Assuming the end of all cells is ",", =IF(Right(A1,2)=",,",left(a1, find(",,",a1)-1),Left(A1,Len(A1)-1))

Re: Removing Trailing Commas

=left(a1,find(",,",a1)-1)

Re: Biggest Loser

Our office has the same thing, so I did this for it. Does this help?

## Files

Re: Increment By 1 The Variable in Every 503 Rows

1. Yes, it is normal. It will return either True or False. You would drag this down through all 503,000 rows.
2. On the Home tab, toward the right is a sort and filter button. Hit Ctrl+A, then this button, then select Filter. In B1, filter to TRUE.
3. Step 2 will give you 1,000 rows as the formula will be true only 1000 times.
4. This is not a macro; a macro would do this all automatically. You did not request this text to be entered in your original post. If you need that as well, I could suggest another couple of steps, but you would probably be better off going for a macro at this point. I'd help, but I'm not good enough to do the second part of what you want yet.

Re: Increment By 1 The Variable in Every 503 Rows

As a macro-free solution, in column B, you can put =MOD(ROW(),503)=0 and drag that down. Apply filters to the sheet and filter to TRUE. Highlight all 1000 rows, hit Ctrl+; to select only visible cells, then Alt, I, R to insert rows. Then delete column B.

Re: Match Values In Two Columns

If your first list is in A1:A4000 and the second is in B1:B300, C1 could have =VLOOKUP(B1,A\$1:A\$4000,1,False). That will return an error message for every item in B that doesn't show up in A. In D1, =VLOOKUP(A1,B\$1:B\$300,1,False). Then you can just filter to the error messages and delete.

Re: Difference Date

=(c2+d2-a2-b2)*24