Count function

[Omer]

How can I make Excell count the difference between
subsequent rows and do two things

Data at the end

1. Tell me how many times the value as a result of
subtraction (A2-A3, A3-A2, etc) was >0
2. What is the Sum of those >0

Expected result: Based on this data there were 5 instances
where the vaule was >0 and the sum is $$$

Thanks

58.63
58.27
58.66
59.21
59.73
59.57
59.43
60.38
60.64
60.37
61.14
61.63
61.46
60.75
60.85
59.93
59.35
59.70
59.05
59.20
58.88
58.34
59.74

 

[Tom Ogilvy]

both A2-A3 and A3-A2 can not be > 0. I assumed you meant A2-A3>0, A3-A4>0,
A4 - A5>0, etc.

so you would get a count of all cases where there was a decrease from one
cell to the next.

=SUMPRODUCT((abs(A1:A49-A2:A50)>0)*(A1:A49<>"")*(A2:A50<>""))

will give you the count

=SUMPRODUCT((A1:A49-A2:A50>0)*(A1:A49<>"")*(A2:A50<>"")*(A1:A49-A2:A50))
will give you the sum of the decreased amounts.

I get 11 instances with a sum of 4.82

 

[Tom Ogilvy]

Trying to figure out what you want, I screwed up the first formula by
putting in ABS and forgot to chang it back.

You apparently, based on your multiposts of similar messages to misc and
worksheet.functions, want A3-A2, A4-A3, etc

=SUMPRODUCT((A2:A50-A1:A49>0)*(A1:A49<>"")*(A2:A50<>""))

will give you the count

=SUMPRODUCT((A2:A50-A1:A49>0)*(A1:A49<>"")*(A2:A50<>"")*(A1:A49-A2:A50))

will give you the sum of the positive differences.

This includes A2-A1, if you don't want that then adjust the formula as
follows:

=SUMPRODUCT((A3:A50-A2:A49>0)*(A3:A50<>"")*(A2:A49<>""))

 

[Omi]

Tom,

First of all tahnks for the help

I know I am doing something stupid, but please help me out.

I entered the formula (Like I though U suggested) and I
get 22. Please tell me what I am doing wrong

=SUMPRODUCT((ABS(A19:A41-A20:A42)>0)*(A19:A41<>"")*
(A20:A42<>""))

58.63
58.27
58.66
59.21
59.73
59.57
59.43
60.38
60.64
60.37
61.14
61.63
61.46
60.75
60.85
59.93
59.35
59.7
59.05
59.2
58.88
58.34
59.74



Thanks Again

 

[Tom Ogilvy]

see me most recent post in this thread - that was a mistake because I wasn't
sure exactly what you wanted.

anyway, for your adjusted ranges it would be:

=SUMPRODUCT((A20:A42-A19:A41>0)*(A19:A41<>"")*(A20:A42<>""))


Regards,
Tom Ogilvy