Data Analysis Problem

[GapWolf]

Good morning everybody,

I have been wrestling with this issue for a while and joined this forum to see if anybody can help - I would be very grateful.

I have attached a sample spreadsheet with the type of problem that I am trying to analyse. I have data associated with specific days and the spreadsheet that I am trying to analyse can have any number of days with each day having any number of entries. What I would like to do is to find for each individual day the number of occurences of say "-8".

What I would like to be able to create is a report with Day, Number of Entries in Day, Number of Occurences of "-8".

As a bonus I would like to know if the occurnces of -8 are sequential and if so how long is the sequence.

I have had a complete blank as far as Excel being able to anlyse this and have thought that I will need to use, say, Python for it. I would appreciate any help that can be provided.

Thank you.

[benishiryo]

hi GapWolf, welcome to the forum. see if the 2 pivot tables help u. i have no idea how to find if the number of sequential occurrences though

[JBeaucaire]

1) Highlight column A
2) Apply a Data > Sort & Filter: Advanced filter with these settings:

Copy to another location
Copy to: F1
[x] Unique Values only

Now you have the unique dates in column F.

2) Add the titles in G1:I1

Count
-8
Seq

3) Formulas:

G2: =COUNTIF(A:A, F2)
H2: =COUNTIFS(A:A, F2,B:B, $H$1)
I2: =SUMPRODUCT(--($A$2:$A$59=F2), --($B$2:$B$59=$H$1), --($B$2:$B$59=$B$1:$B$58))

Notice that last formula, the last range is offset by one row for the sequential comparisons.

[JBeaucaire]

Thanks for the feedback. If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.