Summing and Counting but trying to exclude zero values or real values if the adjacent value is zero.

Question

Level 1

51 points

Summing and Counting but trying to exclude zero values or real values if the adjacent value is zero.

I have a table that is working out the breakeven value of a series of numbers in two columns (multiply the values in the two columns then divide by the count of the second column).

I am trying to make it idiot proof so that if a 0.0000 is in a row in the first column but mistakenly a 1 or 2 or 3 etc appears in the next column, that "1 or 2 or 3" isn't included in the count and used as part of the denominator (because it's associated with a 0.0000 in the first column). Therefore the division number is really 3 (from the first two rows) not 4 (from the incorrect "1" in the last row). Ditto not including adding up the irrelevant 1.2500 in rows 73 an 74.

= (IF(C70 > 0, C70 * D70, 0) + IF(C71 > 0, C71 * D71, 0) + IF(C72 > 0, C72 * D72, 0) + IF(C73 > 0, C73 * D73, 0) + IF(C74 > 0, C74 * D74, 0) + IF(C75 > 0, C75 * D75, 0) + IF(C76 > 0, C76 * D76, 0) + IF(C77 > 0, C77 * D77, 0) + IF(C78 > 0, C78 * D78, 0) + IF(C79 > 0, C79 * D79, 0) + IF(C80 > 0, C80 * D80, 0)) / COUNTIF(C70:C80, ">0")

Any help would be great as I've gone round in circles for the last few hrs!

MacBook Pro 15″

Posted on Feb 19, 2024 5:20 AM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,456 points

Feb 21, 2024 9:24 AM in response to Bardonicloud

It is SUMPRODUCT(C70:C80,D70:D80), not SUMPRODUCT(C70:C80)

This is C70*D70 + C71*D71 + ... +C80*D80.

If C or D in a particular row is zero then the multiplication of them is zero. If the price is zero or the position size is zero on that row, that row will not add to the sumproduct.

It is the denominator that I'm confused about. You want to count only rows where there is a trade and not count those that have no trade. Here is I have from what you posted:

1) I am going to assume that C=0 and D=0 is a no trade.

2) "A no trade would occur when there is no Price entered in Col C but a 1 or 2 or 3 Position Size has been accidentally left in Col D"

-> No trade when C=0 and D<>0

3) A no trade would occur if "there is a Price in Col D (by intent or accident) and a correct 0 Position Size left in Col D". I assume you meant "there is a Price in col C".

-> No trade when C<>0 and D=0

Does thetable below table accurately reflect what you are saying? If so, you can see there is only one combination that is a trade. You can write a COUNTIFS that counts only the rows that meet those two conditions of C<>0 and D<>0

Reply

Answer 2

Bardonicloud Author

Level 1

51 points

Feb 23, 2024 8:12 AM in response to Badunit

C and D are numbers.

My long hand version of my Breakeven formula works, 1.2533 being correct, yet it has the wrong denominator number ranges: C70:80 is wrong, C70:79 is correct, ditto for Col D's denominator part and yet it still gives a correct result: ?

((C70 × D70 × IF(C70 ≠ 0, 1, 0)) + (C71 × D71 × IF(C71 ≠ 0, 1, 0)) + (C72 × D72 × IF(C72 ≠ 0, 1, 0)) + (C73 × D73 × IF(C73 ≠ 0, 1, 0)) + (C74 × D74 × IF(C74 ≠ 0, 1, 0)) + (C75 × D75 × IF(C75 ≠ 0, 1, 0)) + (C76 × D76 × IF(C76 ≠ 0, 1, 0)) + (C77 × D77 × IF(C77 ≠ 0, 1, 0)) + (C78 × D78 × IF(C78 ≠ 0, 1, 0)) + (C79 × D79 × IF(C79 ≠ 0, 1, 0))) ÷ COUNTIFS(C70:C80, "<>0", D70:D80, "<>0")

I'm not sure why including the IFS gives the correct answer when your point is valid about C70=0 then it doesn't matter what the Col D Position Size value is?

If the IFS are not needed in the Breakeven calc. when removed though and formatted in my easy to see long hand version below, it gives the wrong answer of 3.7600? Is that because there are not the necessary conditionalities to stop incorrect "Pos Size" Col D errors from being counted? Errors like a 1 or 2 or 3 being incorrectly left in from previous trades and where they're found row adjacent to correctly filled in Col D Prices of 0.000, so the Pos Sizes of "1" are incorrectly added to the denominator?

C70 × D70 + C71 × D71 + C72 × D72 + C73 × D73 + C74 × D74 + C75 × D75 + C76 × D76 + C77 × D77 + C78 × D78 + C79 × D79 ÷ COUNTIFS(C70:C79, "<>0", D70:D79, "<>0")

= 3.7600 (1.2533 correct).

Using your formula of SUMPRODUCT(C70:C80,D70:D80) has no conditionalities that I require to mitigate errant Pos Size Col C errors and that produces 3.7600 in cell W80.

I hope this is clearer now, cheers,

Reply

Answer 3

Bardonicloud Author

Level 1

51 points

Feb 22, 2024 3:53 AM in response to Badunit

Thanks for this, I was just referring got the Col C half of the whole SumProduct, but see the confusion.

1) & 2) Yes

3) “there is a Price in Col D”. My bad, I did mean Col C, thanks, it solves the problem of a pesky 1 or 2 or 3 being left in Col D.

Yes the table is exactly right, great.

((C70 × D70 × IF(C70 ≠ 0, 1, 0)) + (C71 × D71 × IF(C71 ≠ 0, 1, 0)) + (C72 × D72 × IF(C72 ≠ 0, 1, 0)) + (C73 × D73 × IF(C73 ≠ 0, 1, 0)) + (C74 × D74 × IF(C74 ≠ 0, 1, 0)) + (C75 × D75 × IF(C75 ≠ 0, 1, 0)) + (C76 × D76 × IF(C76 ≠ 0, 1, 0)) + (C77 × D77 × IF(C77 ≠ 0, 1, 0)) + (C78 × D78 × IF(C78 ≠ 0, 1, 0)) + (C79 × D79 × IF(C79 ≠ 0, 1, 0))) ÷ COUNTIFS(C70:C80, "<>0", D70:D80, "<>0")

When I try and condense this code below using SumProduct for the numerator part, the top half gives me a 0 instead of the product of C*D...

Reply

Answer 4

Badunit

Level 7

30,456 points

Feb 22, 2024 6:05 AM in response to Bardonicloud

Are C and D numbers or text? COUNTIF doesn't care. The math operators (+-*/) don't care. SUMPRODUCT does. They need to be actual numbers.

I need to comment on the last formula you posted, though. Your formulas are overly complicated. Think about the logic a little more.

Take the first part, C70*D70*IF(C70<>0,1,0).

If C70<>0 then the IF=1 and you get C70*D70

If C70 = 0 then the IF=0 and you get 0. But C70=0 so the result of C70*D70=0 all by itself without the IF.

All you need is C70*D70, the IF part of the formula is totally worthless.

SUMPRODUCT(C70:C80,D70:D80) will do all those multiplications and additions in one simple function.

Reply

Answer 5

Bardonicloud Author

Level 1

51 points

Feb 21, 2024 8:46 AM in response to Badunit

Excuse the minimal explanation. I am trying to avoid any "no trades" being used in the breakeven calculation.

A "no trade" would occur when there is no Price entered in Col C but a 1 or 2 or 3 Position Size has been accidentally left in Col D or, when there is a Price in Col D (by intent or accident) and a correct "0" Position Size left in Col D.

Without a Position Size there is no trade but I'm guessing a 1 or 2 or 3 etc could be left in Col D by accident (next to a 0.000 Price) and then the breakeven will be incorrect.

Yes Rows 73 and 74 are irrelevant as there is no corresponding Position Size entered in Col D and therefore no trade has been taken or can be multiplied for that row. I am trying to avoid the easy mistake of dragging down/using a previous price, eg from C71 and leaving it in eg C72, even though there is no Pos Size 1, or 2 or 3 etc next to C72 and no trade is intended.

However Col D mistakes (where a Price eg 1.2700 is entered/left from the past, with no corresponding Pos Size, probably actually won't matter or be added incorrectly if I use Col V to calculate a running summation of breakeven values row by row.

The real problem is with Col D. There might be a pesky 1 or 2 or 3 etc left in an adjacent Cell, inadvertently, when there shouldn't be (regardless of whether Col C has 0.000 or eg 1.2700). This incorrect 1 or 2 or 3 etc then incorrectly becomes part of a denominator that is now too large by 1 or 2 or 3 etc giving a false breakeven.

SUMPRODUCT(C70:C80)

I'm thinking calculating row buy row trades sizes (entry Price x Pos Size) for Col V might mitigate the need for SUMPRODUCT(C70:C80)

The code needs to recognise that a Col D: 1 or 2 or 3 is "fake" because it's directly next to a Price that is 0.000 in Col C. That was the bit I'm stuck on coding.

Cheers for the help,

Reply

Answer 6

Badunit

Level 7

30,456 points

Feb 19, 2024 8:01 AM in response to Bardonicloud

Your description of the problem and what you said the answer should be don't seem to be the same thing. You said you don't want to count rows where column C is a zero. You said the correct result from your screenshot should be 3 but I see 5 entries in column C that are not zero. You said rows 73 and 74 are "irrelevant" because they have a zero in column D. Apparently row 72 is relevant but I don't see how it differs from 73 and 74. If you want to count only those rows that are non-zero in both columns C and D, use COUNTIFS. But this will exclude row 72 and the count will be 2, not 3.

For the numerator, you can use SUMPRODUCT(C70:C80,D70:D80) in place of all those IF functions unless the values in C may be negative and that is what you are using the IF functions for. If C is always >=0 then checking for C>0 is not required. If C>0 then the result is C*D. If C=0 then the answer is still C*D (which will be 0), which is the answer you want. SUMPRODUCT can do all the multiplications and sum them up in one function.

Reply