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

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.

61089

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

  • ElapsedTime Forum.xlsx
    16.8 KB · Views: 5
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

  • Copy of ElapsedTime Forum-1.xlsx
    18.2 KB · Views: 7
Last edited:
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

  • Rev of ElapsedTime Forum-1.xlsx
    13.8 KB · Views: 6
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
 
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.
 
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.
 
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!

!61635
 
Last edited:
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)
 
Back
Top