Table Information
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Table Information
which formula can I use to map selected information from table for "Idle Time" only when the duration is greater than 0:05:00 for selected name.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Table Information
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Thanks. Works Great
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
What's this type of formula called ?
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Table Information
It's called a dynamic array formula. Such formulas are new in Excel in Microsoft 365 and Office 2021 (and Excel Online).
See Dynamic array formulas in Excel
See Dynamic array formulas in Excel
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Can I edit the formula to accommodate time period ?
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Table Information
Sure! Let's say you enter the start date in B1 and the end date in B2. You can then use
=LET(r,G5:M462,f,INDEX(r,SEQUENCE(ROWS(r)),{3,7,1}),FILTER(f,(D5:D462=R3)*(G5:G462>TIME(0,5,0))*(H5:H462="Idle time")*(I5:I462>=B1)*(I5:I462<=B2),""))
See the attached version.
=LET(r,G5:M462,f,INDEX(r,SEQUENCE(ROWS(r)),{3,7,1}),FILTER(f,(D5:D462=R3)*(G5:G462>TIME(0,5,0))*(H5:H462="Idle time")*(I5:I462>=B1)*(I5:I462<=B2),""))
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Can i add sumifs to the filter ? so instead of showing 1 name(column D), it list all names with the total idle for a selected date.
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Please see atached
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Table Information
In the attached version, I have added two solutions: one using a pivot table, the other using formulas.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Thanks.
How do i eliminate a a specific criteria using the below formula.
Eg. Leave out staff 5
Please use the same table you used to write the below formula from earlier in this discussion.
=LET(r,G5:M462,f,INDEX(r,SEQUENCE(ROWS(r)),{3,7,1}),FILTER(f,(D5:D462=R3)*(G5:G462>TIME(0,5,0))*(H5:H462="Idle time")*(I5:I462>=B1)*(I5:I462<=B2),""))
How do i eliminate a a specific criteria using the below formula.
Eg. Leave out staff 5
Please use the same table you used to write the below formula from earlier in this discussion.
=LET(r,G5:M462,f,INDEX(r,SEQUENCE(ROWS(r)),{3,7,1}),FILTER(f,(D5:D462=R3)*(G5:G462>TIME(0,5,0))*(H5:H462="Idle time")*(I5:I462>=B1)*(I5:I462<=B2),""))
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Assuming the formula was modified to list name and idle intervals by date.
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Table Information
That formula already filters just one staff name: the one listed in R3.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 19
- Joined: 30 Aug 2023, 20:25
Re: Table Information
Can subtotal be added at the bottom of a dynamic array formula ?
-
- Administrator
- Posts: 78582
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Table Information
If you have Microsoft 365:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans