• 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.

Counter in every cell based on multiple criteria

V

Vojin

New Member
Hi,


While making a Car park Maintenance data record in Excel I encountered a problem that’s beyond my scope of Excel knowledge. So after stumbling on your awesome site I thought of asking you guys for a bit of help.


From the stored data I need to extract the Hours of work for a particular vehicle and no. of failures per vehicle i.e. no. of times that for ex. Mercedes/ Actros/ 2041/5-105/Maint. has broke down. The tricky bit is how to extract the data about both Hours of work and No. of failures every time a particular vehicle has broken down on a separate sheet and each in one column. In other words the first time Actros 2041 brakes down the indicator in let’s say Sheet2! C3 has to indicate 1 (breakdown), next time the same vehicle brakes down again Sheet2! C4 will have to indicate 2 (breakdowns) and so on. In the same time in the adjacent column for calculating hours of work one has to apply the same filtering criteria and when all of them are met to calculate the simple difference between two values of working hours for a particular vehicle.

Data: Sheet1-‘Data collection’ contains the following type of data, amongst many others:

1. Manufacturer/Model/Type/License Plate no./Company’s No./Mileage/Hours of Work/Dept./…

2. Mercedes / Actros / 2041 / -- / 5-105 / 28.500 / 42 / Maint.

3. VW / Polo / 1,4 Tdi / BG-352GC / 3-013 / 126.526 / -- / GH

4. Skoda / Octavia / 1,8 TSI / -- / 5-198 / 5.138 / -- / Maint.

5. Mercedes / Axor /1844 / -- / 5-106 / 256.433 / -- / Maint.

6. Mercedes / Actros / 2041 / -- / 5-105 / 28.751/ 51 / Maint.

And there are over 150 vehicles, more than 40 manufacturers, nearly 30 types of vehicles in the car park for which it would be necessary to extract the data in order to assess the readiness of each and every vehicle as well as the car park as a whole.

No. of breakdowns per vehicle and hours of work (between 2 consecutive breakdowns) are to be calculated after the particular vehicle meets the following set of criteria: 1. vehicle model and 2. type and 3. Dept. and 4. license plate no. or company no (depending on the fact if it’s registered or not).


The result should look like this:

Sheet2!

For lets say (Mercedes) Actros 2041 Maint. 5-105

B2: Dates: C2: No. of breakdowns per vehicle: D2: Hours of Work (between 2 consec. breakdowns)

B3: date 1 C3. 1 D3: 0 (there aren’t any previous data)

B4: date 2 C4. 2 D4: 9

B5: date 3 C5. 3 D5: hw3-hw2

And so on.


I tried the countifs, sumifs, vlookup combined with countifs and sumproduct but I always got the result in one cell rather than sort of a counter of no. of breakdowns for a particular vehicle in every cell.
 
Hi ,


Thanks for an interesting problem. I don't think there is any problem in going through your detailed description but , to paraphrase the saying , an upload is worth a thousand words !


Can you please upload a sample worksheet containing around 20 rows of data ?


Narayan
 
Totally agree. I even prepared a file to upload but forgot it at work. Anyway I managed to fill some data in the worksheet that resemble the real ones. When filled with raw data it'll look something like this: http://hotfile.com/dl/142015185/3e96571/Vehicle_maint-eng.xlsx.html or https://rapidshare.com/files/3762528361/Vehicle_maint-eng.xlsx .


Hope it'll be a bit clearer now.


Cheers,
 
Hi Vojin ,


I think a few more clarifications may be needed.


1. The first measure is Th - the total number of HoW of vehicles in the observed time interval ; for this the "observed time interval" (oti) is to be defined ; what is oti ?


2. For Th , the column to be used is M , is that right ?


3. For the second measure Notk , what is to be done ? Is it just a count of the number of records for a particular vehicle ?


4. How is the third measure Nopr different from Notk i.e. for the same breakdown record , are there multiple repair records ?


5. For the fourth measure MTBF , the first entry will have no MTBF because there is no previous entry , but thereafter , what is to be done ? Suppose for a particular vehicle , the first entry was in row 17 , and the next entry was in row 25. The entry in row 17 will have an entry for HoW , say 66 ; suppose the entry in row 25 is 173. How is the MTBF to be calculated ?


6. For MTTR , which column is to be used ?


Narayan
 
Hi Narayank991,


first of all thanks for trying to help me and sorry I didn't elaborate the meaning of the KPI's that need to be calculated. Here are the answers to your questions:

1. "oti" is calculated as a simple subtraction of two consecutive values for HoW of a particular vehicle, i.e. the same vehicle. For ex. if we look at Mercedes Actros (company no. 5-105) we notice that in M1 it has a value of 42 whereas in M12 (its second breakdown) it's 52. So T(h)1, cell 'KPI Calculation'!B5, in this case would be 10. Next value in cell B6='Maintenance Data'!M17-'Maintenance Data'!M12=60-52=8 and so on.

2. Spot on,

3. This is the problematic part. Notk is number of breakdowns for a particular vehicle in a specific moment in time. Column 'KPI Calculation'! C has to have a unique value in each cell. For a given example in point 1., cell C5 has to have a value of 1 whereas C6=2, C7=3 and so on. It is in fact a counter of a particular vehicle's no of breakdowns in a moment in time we're looking at, i.e. every time the vehicle breaks down its value should be increased by 1 and displayed in a separate cell.

4. Nopr is counter of no. of repairs of a particular vehicle. The principle is that every time that a value for Date and Time of Operation (columns D&E in Maintenance Data Sheet)are entered it really means that the vehicle is repaired and is returned to operation. This further means that the Nopr counter should increase its value by 1.

5. For the first entry of MTBF you're right, it has to be 0. Afterwards MTBF is calculated as T(h)/Notk. For our example it would be as simple as B5/C5.

6. MTTR is calculated as Trepair/Nopr (in other words a time period a vehicle spent out of operation i.e. time period needed for repairs to be completed divided by counter of a no of vehicle's repairs, Nopr - in some way similar to Notk.). So I kind of messed up big time because I didn't add another column that would calculate the elapsed time a vehicle was inoperable(for ex. Trepair could be calculated as a date and time difference in columns D and B and E and C i.e. subtraction of the date the vehicle was returned in operation ('Maintenance Data'!D4) and date the vehicle broke down ('Maintenance Data'!B4) plus the diffenrence between time the vehicle was returned in operation('Maintenance Data'!E4)and time the vehicle broke down ('Maintenance Data'!C4). Trepair should also be in Hours as is T(h).


Hope the explinations I gave are going to be of some use for you.


Cheers,


Vojin
 
Narayank991,


just to clarify your Q4. Notk and Nopr are both counters but of different things. Notk counts no of breakdowns a vehicle had and Nopr counts no of repairs that same vehicle had. In some way an existing value for Nopr says that a vehicle has been repaired and that that case is closed. But sometimes the time difference between breakdown and repair can be few month because of various reasons(procurement process and things like that).

No, for the time being there is only one repair record per breakdown record in which it is stated what was repaired.


Vojin
 
Hi Vojin ,


Thanks for the clarifications. I'll take some time to digest all this ! In the process , more doubts may come up !


Narayan
 
You must log in or register to reply here.
Back
Top