Formula for acount only the positive difference petween rows then outputing a sum of said difference

[SidneyK]

Formula for a count of only the positive difference between rows then outputting a sum of the difference.

1. I am using Excel 07 on a Win 7 system.
2. On a monthly basis we must report our public wifi user statistics. (we are a municipal library)
3 We download our report directly from the wifi equipment. This report contains 2500-ish rows of data. Below is a small sample of the report data.

[TABLE="width: 90"]
<tbody>[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]

For all that data the positive difference is only 7. Making that 7 total users accessing public wifi.

The report always has a 1 that is the equipment so we do not count 1 we only count the positive difference. If we could count only the positive difference rows of data we would have the correct number of public users.

So far, for months I have just been counting the reports by hand. Needless to say its been mind numbing and very time consuming. If anyone could help me out you would have my eternal gratitude. If what I am trying to do is not feasable just let me know and I will go back to counting by hand.

Thank you for your time in just reading this

 

[AlphaFrog]

Hi and welcome to the forum.

Try this where your data is in A1:A46

=SUMPRODUCT((A1:A45-A2:A46>0)*(A1:A45-A2:A46))

Note: the two ranges are intentionally offset by one row.

 

[Andrew Fergus]

Hello and welcome to MrExcel

There are a few ways to solve this.

One method is to use a helper column, assuming your data is in column A starting at row 2, then enter the following formula into the cell B3 :

=IF(A3>A2,A3-A2,0)

and copy down to the last row iof your data.

This will display the difference between two rows where the lower row has a greater value than the higher row. Then all you need to do is sum the values in that column (I'm guessing you know how). You will need to add 1 to the asnwer given the number never goes below 1 per your post.

There are plenty of other methods but this allows you to see what is going on.

I trust this helps
Andrew

P.S. I see another answer has been provided while I was typing mine; it is a single formula solution that does not require a helper column.

 

[SidneyK]

I want to thank you both so much for saving me a prodigious amount of time on these reports.

Sorry Mr.Frog but I was unable to get yours to work. Probably just user error.
Thank you Mr. Fergus it worked like a charm. After reading it it seems so obvious but I have been doing these reports by hand for the better part of 6 months. Boy do I feel stupid now.

Thank you both again. I do sincerely appreciate it.