Cyberpunk001
New Member
- Joined
- Aug 27, 2018
- Messages
- 13
Good day experts,
I have great need for a formula that outputs either TRUE/FALSE based on some criteria, the sample table is as follows:
<tbody>
</tbody>
The formula must 1st check in column "EMP ID" for a unique ID, such as 0001, then based on that filter check in the WEEKDAY column between 1-6 to filter the last check, which is the HRS column.The formula will check in the "HRS" column for any max hours, such as 9. IF there are multiple instances of the same max occurring, check for the very 1st instance from the 1st row from the top of the unique EMP ID, such as A2 where the 1st instance of EMP ID 0001 is found. From there the formula must check row by row downwards to compare each HRS output to the one above and the 1st one to check if the same hours matches up until all the rows for the unique EMP ID has been checked. In this example the formula will see that there are indeed 4 instances of 9 hours, then it will output FALSE for the 1st instance, and TRUE for the rest. The same is done for the minimum value.
Here is the formula I have come up with so far...the only part not working correctly is the absolute referencing:
IF([@HRS]=MAXIFS(O:O,A:A,[@EMP ID],X:X,"<>7"),COUNTIF($O$83:$O83,O83)>1,IF([@ HRS]=MINIFS(O:O,A:A,[@EMP ID],X:X,"<>7"),COUNTIF($O$83:$O83,O83)>1,"NOT"))
Where:
O:O is the HRS column containing all hours for all EMP IDs
A:A is the EMP ID column containing IDs
X:X is the weekday column containing all weekdays for all EMP IDs
The problem I cannot solve so far is COUNTIF($O$83:$O83,O83)>1. So this part of the formula should absolute reference the VERY 1ST ROW of the HRS column, based on the very 1st unique ID, in other words B2 in the above sample table, then go down row by row checking for a maximum value and returning FALSE for the 1st result, TRUE for subsequent results, the same for the minimum value. However this is not happening as the $083
On the output column, the "NOT" is the result checking the "WEEKDAY" column for 7 vs the unique ID and returning "NOT". Because it is not relevant.
Here is what I am hoping you experts can help me with, the formula should output the following from the sample table above, for D5:
IF([@HRS]=MAXIFS(HRS,EMP ID,[@EMP ID],WEEKDAY,"<>7"),COUNTIF($D$2:$D5,D5)>1,IF([@ HRS]=MINIFS(HRS,EMP ID,[@EMP ID],WEEKDAY,"<>7"),COUNTIF($D$2:$D5,D5)>1,"NOT"))
result: TRUE
Reason is that the formula has filtered the unique id to 001, checked in the HRS column for weekdays 1-6, based on those 2 criteria checked the HRS column for a maximum value from B2:B5, returned FALSE for the 1st max value found, and TRUE for subsequent maximums, and done the exact same for the minimum value(s)
Lastly, due to the actual sheet being in a dynamic table, the same formula is repeated for every row.
Genuinely hoping this makes sense.
Any help will be greatly appreciated
I have great need for a formula that outputs either TRUE/FALSE based on some criteria, the sample table is as follows:
| (A1)EMP ID | HRS | WEEKDAY | OUTPUT |
| 0001 | 9 | 1 | FALSE |
| 0001 | 9 | 2 | TRUE |
| 0001 | 9 | 3 | TRUE |
| 0001 | 9 | 4 | TRUE(D5) |
| 0001 | 6 | 5 | FALSE |
| 0002 | 6 | 1 | FALSE |
| 0002 | 7 | 3 | NOT |
| 0002 | 8 | 7 | FALSE(D9) |
<tbody>
</tbody>
The formula must 1st check in column "EMP ID" for a unique ID, such as 0001, then based on that filter check in the WEEKDAY column between 1-6 to filter the last check, which is the HRS column.The formula will check in the "HRS" column for any max hours, such as 9. IF there are multiple instances of the same max occurring, check for the very 1st instance from the 1st row from the top of the unique EMP ID, such as A2 where the 1st instance of EMP ID 0001 is found. From there the formula must check row by row downwards to compare each HRS output to the one above and the 1st one to check if the same hours matches up until all the rows for the unique EMP ID has been checked. In this example the formula will see that there are indeed 4 instances of 9 hours, then it will output FALSE for the 1st instance, and TRUE for the rest. The same is done for the minimum value.
Here is the formula I have come up with so far...the only part not working correctly is the absolute referencing:
IF([@HRS]=MAXIFS(O:O,A:A,[@EMP ID],X:X,"<>7"),COUNTIF($O$83:$O83,O83)>1,IF([@ HRS]=MINIFS(O:O,A:A,[@EMP ID],X:X,"<>7"),COUNTIF($O$83:$O83,O83)>1,"NOT"))
Where:
O:O is the HRS column containing all hours for all EMP IDs
A:A is the EMP ID column containing IDs
X:X is the weekday column containing all weekdays for all EMP IDs
The problem I cannot solve so far is COUNTIF($O$83:$O83,O83)>1. So this part of the formula should absolute reference the VERY 1ST ROW of the HRS column, based on the very 1st unique ID, in other words B2 in the above sample table, then go down row by row checking for a maximum value and returning FALSE for the 1st result, TRUE for subsequent results, the same for the minimum value. However this is not happening as the $083
On the output column, the "NOT" is the result checking the "WEEKDAY" column for 7 vs the unique ID and returning "NOT". Because it is not relevant.
Here is what I am hoping you experts can help me with, the formula should output the following from the sample table above, for D5:
IF([@HRS]=MAXIFS(HRS,EMP ID,[@EMP ID],WEEKDAY,"<>7"),COUNTIF($D$2:$D5,D5)>1,IF([@ HRS]=MINIFS(HRS,EMP ID,[@EMP ID],WEEKDAY,"<>7"),COUNTIF($D$2:$D5,D5)>1,"NOT"))
result: TRUE
Reason is that the formula has filtered the unique id to 001, checked in the HRS column for weekdays 1-6, based on those 2 criteria checked the HRS column for a maximum value from B2:B5, returned FALSE for the 1st max value found, and TRUE for subsequent maximums, and done the exact same for the minimum value(s)
Lastly, due to the actual sheet being in a dynamic table, the same formula is repeated for every row.
Genuinely hoping this makes sense.
Any help will be greatly appreciated
