Formula to return FALSE/TRUE based on dynamic absolute referencing in dynamic table

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD](A1)EMP ID[/TD]
[TD]HRS[/TD]
[TD]WEEKDAY[/TD]
[TD]OUTPUT[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]9[/TD]
[TD]2[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]TRUE(D5)[/TD]
[/TR]
[TR]
[TD]0001[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]NOT[/TD]
[/TR]
[TR]
[TD]0002[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]FALSE(D9)[/TD]
[/TR]
</tbody>[/TABLE]

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:)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Forum statistics

Threads
1,223,881
Messages
6,175,159
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top