How to screen out repeated buying/selling signals?

Very simple problem, but I simply can't figure out how.

Say, A1=buy, A2=(space), A3=buy, A4=sell, A5=sell, A6=sell, A7=buy

I want to screen out all the repeated signals, i.e., since I already bought upon A1, I would ignore A3. And sell on A4. After selling on A4, it'd have been sold, and A5/A6 and all subsequent selling signals will be ignored until a buying signal comes to tell me buy again.

So, B1=buy, B2, B3=(space), B4=sell, B5, B6=(space), B7=buy

What formula do I have to put on row B to achieve this result? This single thing would improve the efficiency of my excel technical analysis model a million times, since I now need to screen them out manually, on dozens of stocks, each consisting of dozens of technical models. A million thanks.

Put this in B1:

=A1

and this in B2:

=IF(OR(A2="",A2=LOOKUP("zzz",A$1:A1)),"",A2)

Copy B2 down as far as you need to.

Hope this helps.

Pete

Thanks a lot Pete. Works like magic and that'd really help a lot. The magic power of the LOOKUP function is still beyond my comprehension at this point.
What if the buying and selling signals are written in a language other than English? Like written in two single Chinese characters each representing “buy” and “sell” respectively. What should I put to replace the “zzz”? Thanks again.

I don't know anything about Chinese characters, but the "zzz" in the English version is just a "large" text value, i.e. the last letter of the alphabet repeated three times. Perhaps you can apply this to the Chinese version.

Hope this helps.

Pete

Great help Pete! I found combining the two characters in place of "zzz" perfectly works for me. Simple solution, great results.

Yet in practice now I have another problem. Those signals in row A are not actually plain words “buy”, “sell”, but are formula defining when they would display “buy” and when they would display “sell”. The LOOKUP formula we have works on plain words situation but not formula situations. What else should be put to make that work? It’s great to see the progress and thanks again!

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

You can put this array* formula in cell L28:

=IF($K28="dieflower",IF(INDEX(L:L,MAX(IF(L$27:L27<>"",ROW(L$27:L27))))="sell","","sell"),IF($K28="flower",IF(INDEX(L:L,MAX(IF(L$27:L27<>"",ROW(L$27:L27))))="buy","","buy"),""))

* Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter.

Then you can copy it down to the bottom of your data. It will prevent multiple occurrences of "buy" or "sell", so your statistics should now work correctly.

Hope this helps.

Pete

Yes, that helps a great lot. Thanks so much Pete, you’re such an expert.

If I could bother you for one more time, what formula should I put on column N so that it starts to show a percentage only after the first selling signal which has a buying signal in prior? Now I have to manually delete the error in N31, resulting in hundreds of models which need similar manual correction. Thank you again.

You can use this formula in cell N28:

=IF(AND(L28="sell",COUNTIF(L$28:L28,"buy")),M28/M27-1,"")

and copy it down to the bottom of your data. It will only calculate where there is a "sell" in column L AND there is a "buy" before it, which I think is what you want.

Hope this helps.

Pete

P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

That works perfectly. You alone save me tons of time! I’m very grateful. Yes that’s really cool to have a way to thank the contributors! They limit me giving one reputation at the moment and I wish to give you on more posts here! Thank you so much again.

Glad to help. You can't give consecutive reps to the same contributor - you have to "spread" them around a bit !

Pete