Elapsed time of the failure event.

herclau

Member
Elapsed time of the failure event.
I must report the elapsed time of each one of the different registered failures. I've done so far with the help of the subtotals command. And manipulating the subtotal function that the result of the command delivers for each subgroup (from SUBTOTAL (9, B2: B7) to SUBTOTAL (4, B2: B7) -SUBTOTAL (5, B2: B7)). For this I relice a macro that locates the subtotal function and replaces it with the one shown.
In the attached file our details!
It is my interest to give solution using the excel functions.
MAXIFS (), COUNTIFS (), SUMPRODUCT (), LOOKUP (), INDEX (,, MATCH ()), etc., etc. These are the functions used in many of the examples seen. But I can not extrapolate them to my particular case.

Attachments

• 16.8 KB Views: 5

GraH - Guido

Well-Known Member
If you don't mind using 2 1 helper columns
[E2: Helper Col: Unique Ref] =IF(C2<>C1,N(E1)+1,E1)
[F2:Helper Col Time] = MOD(B2,1) [EDIT]: you actually do not need it, changed the formula in M2.

[M2: Time] =AGGREGATE(14,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1)-AGGREGATE(15,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1)

[EDIT]: simpler without the nested aggregate...

[N2: IDUFault1] = LOOKUP(AGGREGATE(15,6,\$E\$2:\$E\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1);\$E\$2:\$E\$50,C\$2:C\$50)
[O2: LastFault] = LOOKUP(AGGREGATE(15,6,\$E\$2:\$E\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1);\$E\$2:\$E\$50,D\$2:D\$50) --> drag across from N2

[N2: IDUFault1] = LOOKUP(ROWS(N\$2:N2),\$E\$2:\$E\$50,C\$2:C\$50)
[O2: LastFault] =LOOKUP(ROWS(O\$2:O2),\$E\$2:\$E\$50,D\$2:D\$50) --> drag across from N2

Drag down.

Attachments

• 18.2 KB Views: 7
Last edited:

herclau

Member
If you don't mind using 2 1 helper columns
[E2: Helper Col: Unique Ref] =IF(C2<>C1,N(E1)+1,E1)
[F2:Helper Col Time] = MOD(B2,1) [EDIT]: you actually do not need it, changed the formula in M2.

[M2: Time] =AGGREGATE(14,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1)-AGGREGATE(15,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1)

[EDIT]: simpler without the nested aggregate...

[N2: IDUFault1] = LOOKUP(AGGREGATE(15,6,\$E\$2:\$E\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1);\$E\$2:\$E\$50,C\$2:C\$50)
[O2: LastFault] = LOOKUP(AGGREGATE(15,6,\$E\$2:\$E\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1);\$E\$2:\$E\$50,D\$2:D\$50) --> drag across from N2

[N2: IDUFault1] = LOOKUP(ROWS(N\$2:N2),\$E\$2:\$E\$50,C\$2:C\$50)
[O2: LastFault] =LOOKUP(ROWS(O\$2:O2),\$E\$2:\$E\$50,D\$2:D\$50) --> drag across from N2

Drag down.
I have renamed a column (time by elapsed time) of the report and added a new column (Date).
In the columns date, I report the time in which the failure occurred.
It is possible to improve this function. I had to give Shift + Ctrl + Enter in each row individually to get the expected result. However in the first row it was not necessary.
... ??

Attachments

• 13.8 KB Views: 6

herclau

Member
I have renamed a column (time by elapsed time) of the report and added a new column (Date).
In the columns date, I report the time in which the failure occurred.
It is possible to improve this function. I had to give Shift + Ctrl + Enter in each row individually to get the expected result. However in the first row it was not necessary.
... ??
Fixed the error for the Date column, using the function: [= AGGREGATE (15.6, \$ B \$ 2: \$ B \$ 50 / (\$ E \$ 2: \$ E \$ 50 = ROWS (OR \$ 2: O2)), 1)].

A question:
What is the difference between the AGGREGATE () and SUBTOTAL () functions?
Gracias

GraH - Guido

Well-Known Member
Did you look at the inner help?
Aggregate enables more "aggregations" and has a second argument that allows to filter out elements from the array. In this example argument 6 removes errors from the array, where the divide operator returns "#DIV/0!". From this array without error values the value [k] is taken, a position from the array. There are about 133 calculations possible with the function.

herclau

Member
Fixed the error for the Date column, using the function: [= AGGREGATE (15.6, \$ B \$ 2: \$ B \$ 50 / (\$ E \$ 2: \$ E \$ 50 = ROWS (OR \$ 2: O2)), 1)].

A question:
What is the difference between the AGGREGATE () and SUBTOTAL () functions?
Gracias
AGGREGATE, introduced in Excel 2010, is similar to SUBTOTAL, and has a couple of advantages.

While =SUBTOTAL() offers 11 functions, AGGREGATE() offers 19 functions
. Some of the more useful new functions include SMALL() and LARGE().
In addition to offering you the option of ignoring hidden rows of data, it also allows you the option of ignoring error values and/or subtotals.

herclau

Member
If you don't mind using 2 1 helper columns
[E2: Helper Col: Unique Ref] =IF(C2<>C1,N(E1)+1,E1)
[F2:Helper Col Time] = MOD(B2,1) [EDIT]: you actually do not need it, changed the formula in M2.

[M2: Time] =AGGREGATE(14,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1)-AGGREGATE(15,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1)

[EDIT]: simpler without the nested aggregate...

[N2: IDUFault1] = LOOKUP(AGGREGATE(15,6,\$E\$2:\$E\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1);\$E\$2:\$E\$50,C\$2:C\$50)
[O2: LastFault] = LOOKUP(AGGREGATE(15,6,\$E\$2:\$E\$50/(\$E\$2:\$E\$50=ROWS(N\$2:N2)),1);\$E\$2:\$E\$50,D\$2:D\$50) --> drag across from N2

[N2: IDUFault1] = LOOKUP(ROWS(N\$2:N2),\$E\$2:\$E\$50,C\$2:C\$50)
[O2: LastFault] =LOOKUP(ROWS(O\$2:O2),\$E\$2:\$E\$50,D\$2:D\$50) --> drag across from N2

Drag down.
It is possible to replace this form:
=AGGREGATE(14,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(O\$2:O2)),1)-AGGREGATE(15,6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(O\$2:O2)),1)
by this one
SUM(AGGREGATE({14,15},6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(O\$2:O2)),1)*{1,-1})
and we have the same results.
Why this other one does not work?
SUM(AGGREGATE({4,5},6,\$B\$2:\$B\$50/(\$E\$2:\$E\$50=ROWS(O\$2:O2)),1)*{1,-1})
And the example in the figure works correctly!

! Last edited:

GraH - Guido

Well-Known Member
Firstly, not all aggregate types can handle array calculations. Only this list can: LARGE(array,k) ; SMALL(array,k) ; PERCENTILE.INC(array,k) ; QUARTILE.INC(array,quart) ; PERCENTILE.EXC(array,k) ; QUARTILE.EXC(array,quart).

Secondly from MS help:
Note: The function will not ignore hidden rows, nested subtotals or nested aggregates if the array argument includes a calculation, for example: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)

vletm

Excel Ninja
This herclau 's thread includes same Elapsed time of the failure event. :
Create report from the table by the format of the cells