I thought this was a straightforward conditional calculation but have spent many hours trying to get it to do what it was supposed to!
Made more confusing by things like:
When I changed valued in the denominator, the C70:80 and D70:80, which should have been C70:79 and D70:79 the first time I corrected it I got the "must be the same length" warning but then when I changed both denominator values, the 80's to 79's later it worked (pls see first image below), although with the wrong breakeven answer because the errant Position Size values of "1" in D77 to D79 should not have been counted because they are adjacent to Prices equalling 0.000. This is confusing me because of your point about:
"<>0" is a single string (not a numbers or boolean). Length is 1 (it is one value)
Obviously the four are not all the same length."
So if the error about lengths of arguments is correct (i.e. because of the "<>0" part) why is my formula now accepted in this image (even thought the correct answer is 1.2533 and not 1.8800)?
Apologies, I should have provided you here with a larger screenshot to make it easier for you to see this formula in the second image below is not for the Breakeven calculation (which are at the far right of my tables). I'm referring to my Summing and Counting thread:
Summing and Counting but trying to exclud… - Apple Community
Using a denominator (3.00) derived from a Sum of Position Sizes total in Col D is simpler and can be used as the denominator part for the Breakeven calc, I the tread above. I was experimenting trying to find the correct formula just for Open Positions in Col D which probably can be written in other neater ways? But this seems to work with Sumifs:
:
I get the general concept of SumProduct, CountIfs, it's just hard sometimes to get them combined with the right syntax to get the answer I am looking for. I confess though I'm not a coder, it's not something that comes naturally to me (syntax). Your help is much appreciated.
