# Support Board

Date/Time: Sun, 29 May 2022 01:43:02 +0000

## [User Discussion] - user2user question regarding excel formula - not a technical SC issue.

### View Count: 2067

 [2013-08-13 20:16:18] #1 namlem - Posts: 94 Hello traders, many times I write alert formulas in a worksheet, assume in cell O3. For example, if price above moving average E3>AA3 then draw an arrow at the price and automatically trigger an alert: =IF(E3>AA3,E3,FALSE) (I know I can write it without IF, but this is just a simplified example, in reality it is more complex). now I want to show only one (first) arrow per 20 (next) bars (sometimes i need to show only one arrow/signal per day), what I do is: =IF(and(E3>AA3,sum(O4:O24)=0),E3,FALSE) the problem is, I noticed that this "sum(O4:O24)" is very CPU demanding operation, especially when I do calculations for 30k or more bars and/or in SUM use more than 100 bars... my question: are there any cheaper ways how to show only one signal per X bars? Thanks
 [2013-08-13 21:42:35] #2 tomgilb - Posts: 3277 There is an easy way to show only the first arrow until there is an arrow in the opposite direction. Example for an up arrow: Put this in P3: =IF(E3>AA3,TRUE,IF(E3
 [2013-08-13 22:41:15] #3 namlem - Posts: 94 tomgilb, thanks for coming for a help! it's a nice tip and it works, but in some cases it's not enough.. for example, within an hour I have 4 15 minutes bars, formula gives FALSE[P6],TRUE[P5],FALSE[P4],TRUE[P3], the arrows will be painted on Q5 and Q3 bars.. 2 times.. at the same I don't want them to be painted next X bars, because I treat those as a one signal. So, I must correct my condition, by adding that arrows/signals not necessary go in a row, gaps can happen.. I hope I made myself clear. PS. also your suggestion requires additional column in a sheet. I stared my sheet when only 16 columns were available, so currently I will not have for each existing formula/column one new column. It's not very important, as soon as SC releases Non.-NET platform I will rewrite the code.
 [2013-08-14 19:56:35] #4 tomgilb - Posts: 3277 The Number of Formula Columns can be set to any value 16-60. (Warning: If you change it, you'll have to edit all of your formulas to reference different columns.) Here is a way to paint n bars with the first TRUE: Put this in S1: 20 This is the number of consecutive bars to paint from the first TRUE, inclusive. Put this in S3: =IF(OR(AND(E4AA3,S4>\$S\$1),AND(E3>AA3,S4=\$S\$1)),1,S4+1) This calculates when to start, and continue, counting bars since the first TRUE. Put this in T3: =IF(AND(S3>=1,S3<=\$S\$1),TRUE,FALSE) This is the column used to paint the bars when TRUE. Yes, this uses 2 columns to achieve your goal, but you have 60 possible. Date Time Of Last Edit: 2013-08-14 20:03:30
 [2013-08-14 21:13:27] #5 namlem - Posts: 94 Hm.. this increment, after condition returned TRUE, is a good idea!! However I measured the execution time between SUM() and your approach and both are more or less equal. Must be I have a bottle neck in other place. Actually I hoped that I missed something and my task could have been solved in easy way.. Something like to mark a check box in "Study Settings" window, for each Subgraph, which says "Display Subgraph's signal only once per X days/bars" :) Thanks tomgilb for your help, really appreciate it!