• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help with counting functions

J

Jeffk

New Member
I have to log in a monthly table each 24 hr day as a column, with 15 minute incremants.
I'm looking for a way to count changes in this table. In other words, if say at 01:15 my table shows 225 as a value and that changes to 345 at 01:30 that is counted as 1. Also, at the end of the day say the 23:45 cell for the 2nd of the month has a value of 550 and at 00:00 for the 3rd of the month the value is 600 that is 1. I hope this makes sense. I'm attempting to uplaod a file table but so far I've been unsuccesful.
 

Attachments

Last edited by a moderator:
Hi Jeffk, and welcome to the forum! :awesome:

Will the table have a number at every cell, or only when a change is made? Your sample file has the layout, but no actual data or examples of how the count would look so I am unsure.
 
formula for 1st day of month would be:
=SUMPRODUCT(1*(B14:B108<>B15:B109))+(B109<>C14)

Copy right for each day.
 
OK, so i gave this a shot. It appears as if the total using this formula is actually one number higher than actual amount of changes. As you see in the attachment, only two changes but total reflects three.
I've tried this with a few random data samples.
Thank you for your help with this, I truly appreciate it!
 

Attachments

Count is correct. The value for day 2 at 0:00 is 0, which is different than value in day 1 at 23:45 (660).
 
Got it. I explained it incorrectly I suppose. So if you look at the table, for the 1st of the month, that particular 24 hr period it should read 2 unless it changed from the previos day at 2345. This is looking at the day ahead. I see that in the formula that you provided now.
 
OK, Luke M Thank you Very Much sir!

I see it, and made adjustments as needed.
Works Wonderful,
Many Thanks!!!
 
Can i bother you again...
If I want to count changes between 0000-0545, 0600-1800, and 1815-2345 in seperate cells how can that be done?
In other words, at the bottom of each days column, I would have 4 cells stacked, 1 to count total changes, 1 to count between each of the times I just listed
 
I'll explain how the formula works, and then you should be able to adjust.
Let's say that you want to look at 100 cells. formula is:
=SUMPRODUCT(1*(First99Cells<>Last99Cells))+(LastCellOfToday<>FirstCellTomorrow)

The last bit is only a special case for when you are going from one day to the next. For the most part, you only need to use the SUMPRODUCT portion, where you compare the first N-1 cells to the last N-1 cells.
 
You must log in or register to reply here.
Back
Top