Calculation based on date and name

[kreases]

Hi,

I am trying to work out the Meant Time Between Failures for Servers during a particulare three month period.

I already work out the MTBF for a particular server for all the data available as follows

IF('Availability Rankings'!C3=0," ",(hrsofservice/COUNTIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A22))/24)

Where the rangetouse can be adjusted to pick out parts of the spreadsheet and A22 is the location of the Server name.

In the main spreadsheet there is a column 'N' which has the Month number in (1 to 12) and I want to do the calculation above based on the server name and 3 months of my choice (eg, 7, 8, 9)

Any ideas.

 

[barry houdini]

I think you've asked a very similar question before.

The answer is still the same, in my opinion.

You need a SUMPRODUCT to replace the COUNTIF function, because SUMPRODUCT can handle multiple criteria.

Your problem then is that you are using a variable range for COUNTIF so you need a way to apply this within SUMPRODUCT so that your ranges for different criteria are the same size

 

[kreases]

Unfortunately I don't know how to integrate the Countif into the SumProduct calculation which is why I entered the question.

 

[barry houdini]

What can rangetouse be?

Would it always be a range within one predetermined column? If so which?

Assuming it's column D, for instance

=IF('Availability Rankings'!C3=0," ",(hrsofservice/SUMPRODUCT(--(INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A22),--ISNUMBER(MATCH(OFFSET(INDIRECT("'Main spreadsheet'!"&rangetouse),,10),{7,8,9},0))))/24)

the 10 in the formula denotes the number of columns from D to N so if your rangetouse is in column E then this needs to be 9 instead.

I'm not sure which sheet is your "main spreadsheet", substitute the correct sheet name

 

[kreases]

rangetouse is just the cell range for the server names (eg A24:A43) I am basically adding together the downtime in column G for all occurrences of a particular server name in column A. I need now to be able to do this with the additional criteria of the three months of my choice from Column N (This column shows the months as 1 to 12)

I am not sure from your previous formula was trying to achieve and what you understood as the rangetouse but it just comes back with a#div/0

The main spreadsheet is called Network Stats Unplanned

 

[barry houdini]

I don't see any reference to column G in the formula you quoted initially, is that the full formula?

How are you defining rangetouse, is that a named range or a single cell with a range shown within it?

I think you probably need a SUMPRODUCT formula which, in essence, should be fairly simple, it would just add together the values in G for the rows where your criteria are satisfied.

However SUMPRODUCT requires all ranges to be the same size so you can't have a variable range for one criteria without that affecting the other criteria.

Would it be simpler, first of all, to find a formula which would give you a result for a fixed range and then work towards a solution with more flexibility?

 

[kreases]

Barry,

I think we are going in circles now but I do appreciate you spending the time. I have to refer you to my original formula for my MTBF calculation. This gives me the calculation for the year. Basically it counts the number of times a server name appears in column A in my main spreadsheet(therefore the number of failures) and I divide this figure into the service hours to get the Mean Time Between Failure.

IF('Availability Rankings'!C3=0," ",(hrsofservice/COUNTIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A3))/24)

The divide by 24 just puts the result into days.

What I want to do is to run the above calculation for not for the full year(spreadsheet to date) but for 3 months of my choice by some how using Column N in my spreadsheet which gives the month number from 1 to 12.

No other columns of my spreadsheet are required for the MTBF calculation.

Thanks again.

 

[barry houdini]

I may be barking up the wrong tree (or possibly it's not even a tree at all ) but I think the previous formula still stands with a little adjustment

=IF('Availability Rankings'!C3=0," ",(hrsofservice/SUMPRODUCT(--(INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A22),--ISNUMBER(MATCH(OFFSET(INDIRECT("'Network Stats Unplanned'!"&rangetouse),,13),{7,8,9},0))))/24)

where rangetouse is a range within column A something like your earlier example A24:A43 and the corresponding range in column N contains numbers denoting the month.

 

[kreases]

Barry,

That worked fine and I can now calculate my MTBF automatically for the 3 month periods, thank you for all your trouble with that. The problem I have now is to calculate the Mean Time to Repair for a server over the 3 month period.

This entails adding all the downtime (in column G) for a particular server name (in Column A) for the specified months (Column N), then dividing this figure by the number of failures to get the MTTR.

To get the MTTR for a server for the year today (complete spreadsheet) I use the following formula,

IF('Availability Rankings'!C3=0," ",(SUMIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A3,'Network Stats Unplanned'!$G$7:$G$500)/COUNTIF(INDIRECT("'Network Stats Unplanned'!"&rangetouse),A3)))

A3 = Server Name
rangetouse = range of cells in Column A of spreadsheet
G7:G500 = range of cells to select downtime from


You got your magic wand handy?

 

[barry houdini]

Based on the formula I suggested above you could try this array formula

=IF('Availability Rankings'!C3=0,"",AVERAGE(IF((INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A3)*(ISNUMBER(MATCH(OFFSET(INDIRECT("'Network Stats Unplanned'!"&rangetouse),,13),{7,8,9},0))),OFFSET(INDIRECT("'Network Stats Unplanned'!"&rangetouse),,6))))

must be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around formula in formula bar

....but this one may be preferable as it replaces OFFSET with SUBSTITUTE....also must be confirmed with CTRL+SHIFT+ENTER

=IF('Availability Rankings'!C3=0,"",AVERAGE(IF((INDIRECT("'Network Stats Unplanned'!"&rangetouse)=A3)*(ISNUMBER(MATCH(INDIRECT("'Network Stats Unplanned'!"&SUBSTITUTE(rangetouse,"A","N")),{7,8,9},0))),INDIRECT("'Network Stats Unplanned'!"&SUBSTITUTE(rangetouse,"A","G")))))