Count function
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:A490)*(A1:A49<"")*(A2:A50<""))
will give you the count
=SUMPRODUCT((A2:A50-A1:A490)*(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:A490)*(A3:A50<"")*(A2:A49<""))
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
both A2-A3 and A3-A2 can not be 0. I assumed you meant A2-A30,
A3-A40,
A4 - A50, 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:A500)*(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
--
Regards,
Tom Ogilvy
"Omer" wrote in message
...
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
|