IF, AND, OR, AND, AND... calculation??

Hi, I'm trying to insert a function that will calculate the production bonus for my staff. There are several targets that must be met to get the bonus. Just to compound the difficulty the targets can change drop on what product we are running.

I have entered this one so far, which looks at the 3 target numbers.

=IF(AND(OR(I36=0,I36>180),OR(I68=0,I68>60),OR(I100=0,I100>3)),1,0)

If there is no production, target=0, so target is met, and doesn't affect the other parts of the bonus.

if we run Mulch, I36 can drop from >180 to >140. I enter 'Mulch' into I37.

What do I need to include so that this formula looks up the product in I37 and takes this into account.

Thanks

Hi, I'm trying to attach a sample file, hopefully there is enough info on it (had to paste values), as there is lots of conditional formatting and links to other employee's pages.

To try and answer the questions as well,

I36 is either 180bags/hr or 140b/hr if we run mulch on the machine. This does not affect any of the other numbers.

I68 is either 60b/hr or 40b/hr if we run boulders manually. This also does not affect any of the other numbers.

the 'total' numbers are aggegated from the 10 employee sheets, I have included 'Colin' to show where and how the Data is entered.

hopefully this helps a bit more.

AWESOME, you are a scary LEGEND!! Thanks so much, this will cut down at least an hour of trying to scan through a cluttered Excel spreadsheet!!