Forum Discussion
Day Trading Profits
Hi Larry, I have developed a formula that does the calculation. Would you please check if this fits.
I used OFFSET() and XMATCH() to identify the zeros and span the range.
In columns I and J, I used LOOKUP() in case you don't have XMATCH() available.
=IFERROR(IF($N13&""="0",SUM(OFFSET($A$1,XMATCH(0,$N$1:$N12,,-1),0,ROW()-XMATCH(0,$N$1:$N12,,-1),1)),""),"")
Wow! That is AMAZING! Thanks! The only tweak was to change column "N" to "E" in the equation.
That gave me the value in Column F (see file: IFERROR Equation in the link below).
XMatch does work for me.
For column G, it is the sum of columns A+B. I also added the equation to column G and I tried to tweak the equation in various ways, but was unsuccessful. I can get just the commission totals (column B) by changing the $A$1 to $B$1 in column G. I suppose I could do a hidden column and then sum columns F and G. Is there a more elegant solution to get the total of Columns A+B in column G?https://1drv.ms/u/s!AmC9_V2k7OG4iS1PSqZyigjDGE9t?e=pQe8Fj
Hi Larry, the OFFSET() part of the Formula.
OFFSET($A$1,XMATCH(0,$E$1:$E12,,-1),0,ROW()-XMATCH(0,$E$1:$E12,,-1),1)
The syntax is:
OFFSET(reference, rows, cols, [height], [width])
If you want to change the columns that are to be totalled, you only have to change the last parameter. So for column A + B :
OFFSET($A$1,XMATCH(0,$E$1:$E12,,-1),0,ROW()-XMATCH(0,$E$1:$E12,,-1),2)