# Posts by darkyam

• ## Calculate Break Even Point Of Ticket Sales

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.

• ## Calculate Break Even Point Of Ticket Sales

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.

• ## Extract Text After Specific Keyword

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.

• ## Extract Text After Specific Keyword

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.

• ## Change Formula Reference Each Month

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.

• ## Return Decimal Portion Of Number

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.

• ## Return Decimal Portion Of Number

Re: After The Decimal Point

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

• ## Cost Based On Distance Between Zip Codes

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.

• ## Remove Trailing Commas

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.

• ## Running Total Of Weight Loss & Percentage

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.

• ## Remove Trailing Commas

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))

• ## Remove Trailing Commas

Re: Removing Trailing Commas

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

• ## Running Total Of Weight Loss & Percentage

Re: Biggest Loser

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

• ## Group Every Nth Row

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.

• ## Group Every Nth Row

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.

• ## Retain Values Common In 2 Column Lists

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.

• ## Hour Difference Between 2 Date & Time Cells

Re: Difference Date

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

• ## Pad Numeric Portion Of Text & Number With Zeros

=rept(0,5-find(" ",a1))&a1
If there are any addresses that have more than 4 digits, =rept(0,Max(0,5-find(" ",a1)))&a1

• ## Hourly Rate Divided By Total Time

Re: Currency Divided By Time

No, I mean if you post in another forum, add a link to the thread on that forum here and vice versa. If the problem is solved somewhere else, people here should know so that they aren't wasting their time on it.

As for your problem, please post a workbook if at all possible. If none of the things you have tried have worked, then the problem will most easily be solved by seeing the workbook that is causing the issue.