Confused about what method to use for extracting data

[matthewoaks]

Hi,
I am brand new to the forum so please bear with me if I'm a little long winded
I have been looking around on the internet and Youtube to try and figure the best approach I should take for extracting and displaying data. The list is based on observations that come in from auditors and these are recorded to identify trends and repeated observations to try and avoid them in the future.
I'm not sure if this is the best method but I have put the list in a table so that it can be filtered but don't know how to go about summarising the data in either graphical form or a smaller table alongside.
In the table there are 5 main columns; Vessel Name Date of Inspection SIRE VIQ Chapter Question Ref Inspectors Comments
Now each vessel has two inspections per year (6 vessels).
For each inspection a vessel might pick up a number of observations. These obs are split into chapters for the relevant part of the ship ie. 1.General Info, 2.Certification, 3.Crew Management, 4.Navigation and so on (there are 13 chapters in total).
In each chapter there are a number of questions that the auditor surveys the vessel against i.e Chapter 1, Q1.1, 1.2, 1.3, 1.4 and so on for each chapter.

I have been asked to try and display the data so that we can easily spot which vessels are getting repeated observations for a particular chapter and more importantly for a particular question.
If you need me to attach the Excel Workbook then it isn't a problem but I need help on how to do this

Look forward to hearing your suggestions
BRgds
Matt

 

[Rijnsent]

Hi Matt,

The current "is something a Recurring Error" calculation is counting whether the same Question Ref number is recurring for that same ship the Audit before.
=IF(COUNTIFS([Vessel Name];[@[Vessel Name]];[InspNr];[@InspNr]+1;[Question Ref];[@[Question Ref]])>0;"Yes";"No")
In the example file, I added a column that counts the "Recurring Error" as: had the same SIRE VIQ Chapter an error the audit before:
=IF(COUNTIFS([Vessel Name];[@[Vessel Name]];[InspNr];[@InspNr]+1;[SIRE VIQ Chapter];[@[SIRE VIQ Chapter]])>0;"Yes";"No")
Then I added two columns so you can do some calculations with the recurring errors.
Basically the same, just the last bit is different, you can see the difference in your OBSERVATIONS sheet, the columns I and J (and for the sum K and L). I added the columns to the pivot on SHIPOVERVIEW. I didn't update the formulae in TRENDS, but if you want to use the new "repeating error" count, you can change the formula:
D20: =COUNTIFS(TableObservations[Vessel Name];$B20;TableObservations[InspNr];1;TableObservations[RecurQuestion];"Yes")
Would then be:
D20: =COUNTIFS(TableObservations[Vessel Name];$B20;TableObservations[InspNr];1;TableObservations[RecurChapter];"Yes")

The file is here again: https://www.dropbox.com/sh/l7ywfwzfk5j20sr/dJ6Jk0NGyM

Hope that is the proverbial cherry on the pie ?

Cheers,

Koen

 

[matthewoaks]

Hi Koen,
Sorry It's a little bit late but many thanks for your efforts, the spreadsheet is working like a dream

BRgds
Matt

 

[matthewoaks]

Hi again Koen,
So just a day after I posted my little thank you note we had an unusual occurrence. One of our vessel got a zero observations. I need to record that an inspection took place for the trending because it will bring our average down but when I put a line in the OBS table with the ships name it obviously thinks that there has been 1 observation.
Is there a way on the trending to identify this as zero ,I've tried amending the formula myself to reference if the Qs Number had a zero than the trending would say zero but with no luck.
This is the formula that is currently used =COUNTIFS(TableObservations[Vessel Name],$B14,TableObservations[InspNr],1)
with current file https://dl.dropboxusercontent.com/u/86401484/SIRE%20Observation%20Trending%20v4.xlsm

If you could assist with this it would be much appreciated
BRgds
Matt

 

[Rijnsent]

Hi Matt,

That is indeed a new situation. What I did to solve it:
SHIPOVERVIEW : an extra filter on cell D6: does not equal 0
TRENDS:
So the formula for e.g. C9 was:
=COUNTIFS(TableObservations[Vessel Name],$B9,TableObservations[InspNr],1)
and is:
=COUNTIFS(TableObservations[Vessel Name],$B9,TableObservations[InspNr],1,TableObservations[Question Ref],"<>0")
I basically just added "<>0" as an extra condition. That same trick I pulled on various other locations in your TRENDS sheet. Next, there were some percentages that showed an DIV/0 error, so I fixed that with an IFERROR formula (e.g. H20).

The file is here: https://www.dropbox.com/sh/xkm0icjqiuo9yvd/EhV35QF8Eg

I'm glad to read that no errors have been found, that hopefully helps showing the value of the report .

Cheers,

Koen

 

[matthewoaks]

Hi Koen,
So I was close but no cigar. I did something like but without the <> and I didn't work.
Many thanks again for your assistance.
Cheers
Matt