Convert offset in formula into index

[Damon123]

Hi,

Excel beginner here, I have this formula here:

SUMPRODUCT(SUBTOTAL(103,OFFSET(D16:D55,ROW(D16:D55)-MIN(ROW(D16:D55)),,1))*(--(LEN(D16:D55)>0)))

which counts non empty cells in the column D16 to D55 with filtering. However, OFFSET is a volatile function, how do I convert the formula to use INDEX or any other non volatile functions instead?

Any help is greatly appreciated. Thanks!

 

[maabadi]

What you think about this:

=SUBTOTAL(103,D16:$D$55)

 

[Damon123]

What you think about this:

=SUBTOTAL(103,D16:$D$55)

Hi Maabadi,

Thanks for the reply, but I have no idea how to adapt it to another column in my worksheet. That column, column L contains positive, negative and 0 values, so when I want to count only the positive values with filtering, I will use this formula:

SUMPRODUCT(SUBTOTAL(103,OFFSET(L16:L63,ROW(L16:L63)-MIN(ROW(L16:L63)),,1))*(L16:L63>0))

so basically, other than counting the positive values only, I also count the negative values (changing to (L16:L63<0)), and the sum of the positive and negative values (changing to 109).

How do I convert the the formula so I don't use the volatile OFFSET function and instead use other non volatile functions like INDEX, or into another formula altogether that is simpler and non volatile and with filtering?

 

[Fluff]

Not sure there is a non volatile formula for what you want, unless you know what the filter criteria are.

 

[Damon123]

Not sure there is a non volatile formula for what you want, unless you know what the filter criteria are.

Hi Fluff,

Thanks for the reply. This is actually for my trading journal. so for every trade I will record the date and time, the direction (long or short) and the strategy I am using. column L is the profit/loss for the trade, positive means the trade is a winner and negative means the trade is a loser. I have another table at the top of the worksheet that tracks stats like the number of wins, loss, total number of trades taken according to the data I have input for each trade.

So lets say if I want to analyze according to strategy then I will filter for a particular strategy under the strategy column, and see under the stats table at the top, to see how many wins/losses the strategy has etc. then if I want to analyze by direction and for long trades only, I will filter by 'long' in the direction column and again see the stats table at the top. so the filter criteria varies depending on what I want to analyze. The formula I have given is for the stats table at the top under the number of wins, since any positive value in column L is counted as a win.

At first thought the formulas for the stats table would be pretty easy to derive, but the ability to filter made it much harder. Considering all this, is it still possible to have a non volatile formula?

 

[Fluff]

Not that I'm aware of.