Sum of values with irregular row intervals. for Sierra Charts

[mescalante48]

Good day everyone, this is for the uni.

the values on columns (A) and (B) are generated at irregular intervals, I would need to sum the most recent non zero value in the (B) column to the most recent non zero value in column (A) and return the sum of that to the (C) column.

no arrays, no VBA

see the example below.
what would be the formula in (C)?

 

[Fluff]

Is this for Excel, or some other app?

 

[lrobbo314]

Book1 (version 1).xlsb
	A	B	C
1	0	16415	16419.82
2	0	0	0
3	0	0	0
4	4.82	0	0
5	0	16414.5	16419.32
6	4.82	0	0
7	0	0	0
8	0	16417	16421.82
9	0	0	0
10	4.82	0	0
Sheet4

Cell Formulas
Range	Formula
C1:C10	C1	=IF(B1=0,0,B1+INDEX($A$1:$A$10,SMALL(IF($A$1:$A$10>0,ROW($A$1:$A$10)),COUNTIF($B$1:B1,">0"))))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Eric W]

I made a few assumptions, but here's another option:

Book1
	A	B	C
1	0	16415	16419.82
2	0	0	0
3	0	0	0
4	4.82	0	0
5	0	16414.5	16419.32
6	4.82	0	0
7	0	0	0
8	0	16417	16421.82
9	0	0	0
10	4.82	0	0
Sheet13

Cell Formulas
Range	Formula
C1:C10	C1	=IF(B1=0,0,B1+INDEX(A:A,MIN(IF(A1:A10,ROW(A1:A10)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

I think you'll find this is for Sierra charts, in which case you've both wasted your time.

 

[Eric W]

Possibly. How can you tell? Was it the picture of the formula bar?

 

[Fluff]

The image tipped me off, so I had a quick look at the OP's posting history & found Alternative for COUNTIF. no VBA, no array.

 

[lrobbo314]

It was fun to solve and to see Eric W's take. It's never a waste of time for me.

The OP may have wasted HIS time if this doesn't translate to Sierra Charts.

 

[mescalante48]

=IF(B1=0,0,B1+INDEX(A:A,MIN(IF(A1:A10,ROW(A1:A10)))))

I made a few assumptions, but here's another option:

Book1
	A	B	C
1	0	16415	16419.82
2	0	0	0
3	0	0	0
4	4.82	0	0
5	0	16414.5	16419.32
6	4.82	0	0
7	0	0	0
8	0	16417	16421.82
9	0	0	0
10	4.82	0	0
Sheet13

Cell Formulas
Range	Formula
C1:C10	C1	=IF(B1=0,0,B1+INDEX(A:A,MIN(IF(A1:A10,ROW(A1:A10)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

this should work even on the Sierra chart, nevertheless, it returns #ARGS! if B1>0

I'm contacting the SC support team with this solution and hopefully, they will tell me why is not working.

I'm super thankful for your willingness to share your knowledge.

 

[mescalante48]

this should work even on the Sierra chart, nevertheless, it returns #ARGS! if B1>0

I'm contacting the SC support team with this solution and hopefully, they will tell me why is not working.

I'm super thankful for your willingness to share your knowledge.

Eric,

SC support replied.

Post From: Spreadsheet study. Sum of values with irregular row intervals. - Support Board - Sierra Chart

Sierra Chart is a professional Trading platform for the financial markets. Supporting Manual, Automated and Simulated Trading.

www.sierrachart.com

my understanding from their response is that the IF function's calculation logic and/or default behavior might differ from software to software, that being said; could you please explain what IF(A1:A10,ROW(A1:A10)) is doing within your formula?, maybe I can replace it with something that'll do the same but different.

thx!