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

 

[mescalante48]

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.

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!

 

[Eric W]

I think I need to tap out at this point. What that section does is look at each value in A1:A10, and if it's non-zero, puts the row in an array, which the MIN later evaluates. Since you said array formulas don't work in SC, we're out of luck. There are some array enabled functions in Excel, like SUMPRODUCT or LOOKUP, but I don't know if they behave the same way in SC, or even if they exist. I have no way to test, so I'll just wish you good luck.

 

[mescalante48]

I think I need to tap out at this point. What that section does is look at each value in A1:A10, and if it's non-zero, puts the row in an array, which the MIN later evaluates. Since you said array formulas don't work in SC, we're out of luck. There are some array enabled functions in Excel, like SUMPRODUCT or LOOKUP, but I don't know if they behave the same way in SC, or even if they exist. I have no way to test, so I'll just wish you good luck.

Well yeah, I put it on the first post. NO ARRAY.
thank you for the help.

 

[bebo021999]

This employ an extra row at very last row:

Book3
	A	B	C
1	0	16415	16419.8
2	0	0	0
3	0	0	0
4	4.82	0	0
5	0	16414.5	16419.3
6	4.82	0	0
7	0	0	0
8	0	16417	16421.8
9	0	0	0
10	4.82	0	0
Sheet2

Cell Formulas
Range	Formula
C1:C10	C1	=(B1>0)*(SUM($A1:$B$11)-SUM($C2:$C$11))

 

[Fluff]

@mescalante48
In future when posting questions about Sierra Charts, please make it clear in either the title or the post, also you need to post in the General Discussion & Other Applications section of the board.