Formula has too many arguments

[DaleJrFan88]

Is there a way to simplify the following formula (highlighted):

=IF(OR('Payroll detail report'!E2="REGLAR",HOURLYSW2PI!D2=0),'Payroll detail report'!G2,IF(AND('Payroll detail report'!E2="GRAVE1",HOURLYSW2PI!D2>0),'Payroll detail report'!G2,IF(AND('Payroll detail report'!E2="GRAVE2",HOURLYSW2PI!D2>0),'Payroll detail report'!G2,IF(AND('Payroll detail report'!E2="GRAVE3",HOURLYSW2PI!D2>0),'Payroll detail report'!G2,IF(AND('Payroll detail report'!E2="SCHED",HOURLYSW2PI!D2>0),'Payroll detail report'!G2,IF(AND('Payroll detail report'!E2="LEAD",HOURLYSW2PI!D2>0),'Payroll detail report'!G2,IF(AND('Payroll detail report'!E2="OPI",HOURLYSW2PI!D2>0),'Payroll detail report'!G2,""))))

Many thanks!

[6StringJazzer]

I see two issues with the logic here. I would need more information before knowing if it could be simplified further.

First, I see that every IF statement in the red part includes the condition HOURLYSW2PI!D2>0. That means this can be factored out as an IF up front, instead of repeated in every IF, like this:

=IF(OR('Payroll detail report'!E2="REGLAR",HOURLYSW2PI!D2=0),'Payroll detail report'!G2,IF(HOURLYSW2PI!D2>0,IF('Payroll detail report'!E2="GRAVE1",'Payroll detail report'!G2,IF('Payroll detail report'!E2="GRAVE2",'Payroll detail report'!G2,IF('Payroll detail report'!E2="GRAVE3",'Payroll detail report'!G2,IF('Payroll detail report'!E2="SCHED",'Payroll detail report'!G2,IF('Payroll detail report'!E2="LEAD",'Payroll detail report'!G2,IF('Payroll detail report'!E2="OPI",'Payroll detail report'!G2,""))))))))

Further, the outcome of every IF condition you check is 'Payroll detail report'!G2. It might be easier to check for the condition that would result in a blank, rather than checking every condition that would result in 'Payroll detail report'!G2. But I can't tell without more information.

There may be a better way to write this formula if I knew what you were trying to do, but based on pure logic alone this is the simplest I can get using the same general approach. It uses a single AND to group together those checks, instead of a separate IF for each possible value:

=IF(OR('Payroll detail report'!E2="REGLAR",HOURLYSW2PI!D2=0, AND(HOURLYSW2PI!D2>0,OR('Payroll detail report'!E2="GRAVE1",'Payroll detail report'!E2="GRAVE2",'Payroll detail report'!E2="GRAVE3",'Payroll detail report'!E2="SCHED",'Payroll detail report'!E2="LEAD",'Payroll detail report'!E2="OPI", 'Payroll detail report'!G2))),"")

Here is a logical equivalent that gets a little bit tricky, and this is the shortest one you will get out of me. It uses MATCH to see if E2 is one of the possible values, rather than a long OR which requires the cell address to be used in a comparison for each possibility:

=IF(OR('Payroll detail report'!E2="REGLAR",HOURLYSW2PI!D2=0, AND(HOURLYSW2PI!D2>0,NOT(ISERROR(MATCH('Payroll detail report'!E2,{"GRAVE1","GRAVE2","GRAVE3","SCHED","LEAD","OPI"},0))))), 'Payroll detail report'!G2,"")

[DaleJrFan88]

Hi 6SJ,

The formula ties to two separate tabs on a workbook, tabs (sheets) HOURLYSW@PI and Payroll detail report. Yes there is a lot of repetition, but I'm attempting to use aurguments to meet certain conditions; the contions, GRAVE1, GRAVE2, GRAVE3, SCHED, OPI, and so on, all have their own hourly rates (based on the posion named). so My formula says that if on the Payroll Detail Report, column E(2) says 'GRAVE1' then populate the corresponding rate in column G(2). The folrmula was working fine for all the GRAVE positions, but ran into the error when I added more positions.

Hope theis helps.

PS..I Used to stay at the Ritz Carlton at Tysons Corner when I was visiting McClean VA on business...small world.

[bebo021999]

Let's clarify your logic from the formula:
To shorten the formula to understand, say 'payroll detal report' as A, 'HOURLYSW2PI' as B.
If A!E2="REGLAR" or B!D2=0, then A!G2
If B!D2>0, then
............If A!E2 is either "GRAVE1","GRAVE2","GRAVE3","SCHED","LEAD","OPI" , then A!G2
else blank.

Generic formula:
=IF(OR(A!E2="REGLAR",B!D2=0),A!G2,IF(B!D2>0,IF(A!E2={"GRAVE1","GRAVE2","GRAVE3","SCHED","LEAD","OPI"},A!G2,"")))

Replace A,B with your sheet name.

Hope it works.

[6StringJazzer]

Your original error is actually due to misplaced parentheses. That could be solved quickly but you would still have an unnecessarily complex formula.

bebo021999, did you test that formula? I believe that the highlighted part does not work as you intend.

=IF(OR(A!E2="REGLAR",B!D2=0),A!G2,IF(B!D2>0,IF(A!E2={"GRAVE1","GRAVE2","GRAVE3","SCHED","LEAD","OPI"},A!G2,"")))

That expression will return TRUE only if A!E2 = "GRAVE1".