Formula to calculate a sum of differences and count them

0
votes

I have the following Excel spreadsheet:

             A                  B      C      D      E     F     G     H

1  Sales                       500    700    600    450   550   600   500

2  Helper Row (Differences)            40%    -14%   -25%  22%   9%    -17%
3  Helper Row (Counts)                  0       0      1     0    0     0      

4  Count Result                 1

In Row 1 you can see the sales over different periods. In Row 2 the difference between the sales are displayed. (e.g. formula in C2=C1/B1-1).

In Row 3 the formula indicates 2-Following-Periods in which in total the sales drop by >-20%.

In the case above this applies to cell E3 because the sales in cell D2 drop by -14% and in the next period in cell E2 by -25% which makes a total drop of those two periods by -39%. The formula I use in Row 3 is for example E3=IF(SUM(D2:E2)<-0.2,1,0).
Eventually, I use a sum function in cell B4 (B4=SUM(B3:H3)) to count how often the above described criteria is met.

All this works perfectly so far. However, my target now is to get rid of the Hepler Row 2 and Row 3.

Do you know a formula that gives me the count result which meets the above described criteria?

1
excelexcel-formula

1 Answers

0
votes

Assuming that your above demonstrated numbers are in cell B1:H1, you can use following formulae to achieve result without helper cells.

Array formula (CTRL+SHIFT+ENTER and not just ENTER)

=SUM((((C1:H1-B1:G1)/B1:G1)<-0.2)+0)

Or for normal ENTER

=SUMPRODUCT((((C1:H1-B1:G1)/B1:G1)<-0.2)+0)