Multiple If and Large functions in formula don't handle all circum

[Bcosta]

I have the following formula in a small table

=IF($D$38>0,IF($H$27>0,IF(E41=LARGE($E$41:$E$52,1),$D$38,0),IF(H41=LARGE($H$41:$H$52,1),$D$38,0)),0)

The formula looks at D38 to see if there is overtime then it looks to H27 to
see if there is hourly pay (there might not be due to all the pay from this
week being Piecework...but overtime still needs to be calcualted if there are
OT hours). If there is OT and there is hourly pay the formula looks int the
table e41:e52 to determine the department with the most hours and asigns the
OT to that department. If there is not any hourly pay the formula looks to
the table in H41:H52 to see which department had the most piecework pay and
allocates the OT to that department.

The formula works GREAT with one minor flaw. If there are 2 or more
departments tied for the most hours the OT gets incorrectly put to all of the
departments that are tied. I can't think of an easy way to solve this and
was hoping for some help. I welcome any thoughts, and thanks in advance if
you can provide any help!

 

[Luke M]

Could try this:

=IF(AND($D$38>0,$H$27>0,COUNTIF(E$41:E41,E41)<2,OR(AND($H$27>0,RANK(E41,$E$41:$E$52)=1),AND($H$27<=0,RANK(H41,$H$41:$H$52)=1))),$D$38,0)

The COUNTIF will check to make sure the value hasn't already appeared in the
list. I also took the liberty of grouping all your conditions together, as
you only have 2 possible outcomes.

 

[Bcosta]

If d38 > 0 there is overtime and it needs to be allocated. if H27 > 0 then
it uses the hours column (E) to determine where it gets allocated, if H27 = 0
then it uses the piecework dollars column (H) to determine where it gets
allocated. Your formula does not work if H27=0.

 

[Luke M]

Apologies, I have the criteria in wrong place. Should be:

=IF(AND($D$38>0,OR(AND(COUNTIF(E$41:E41,E41)<2,$H$27>0,RANK(E41,$E$41:$E$52)=1),AND(COUNTIF(H$41:H41,H41)<2,$H$27<=0,RANK(H41,$H$41:$H$52)=1))),$D$38,0)