If / or issue on complicated formula

I think this is the final formula issue I have with the complex worksheet I have been working on. The formula's below are a little complex and may be hard to follow, but I believe I'm just missing some syntax on my new if/or varaiable.

I have a formula that works fine =IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),Staff!$H10 * Setup!$C$24 * I$2,0)

Now I have to add another data qualifier (or whatever it's called), so that if cell c10 has a value of "CAFS" in it, I need to change one number in the formula so that the new formula would be:
=IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),Staff!$H10 * Setup!$C$24 * I$3,0)

If the value in c3 is something other than "CAFS", then I need to keep the original formula =IF(AND(I$1>=Staff!$N10,OR(I$1 <= Staff!$O10,ISBLANK(Staff!$O10))),Staff!$H10 * Setup!$C$24 * I$2,0)

So the only difference in the two formulas is which row is used on the last part of the equation (in the first case row "3" and in the second case row "2").

The formula I came up with is: =IF($C6="CAFS",IF(AND(I$1>=Staff!$N6,OR(I$1<=Staff!$O6,ISBLANK(Staff!$O6))),Staff!$H6*Setup!$C$24*I$3,IF(AND(I$1>=Staff!$N6,OR(I$1<=Staff!$O6,ISBLANK(Staff!$O6))),Staff!$H6*Setup!$C$24*I$2,0)))

...which seems to work fine if C6 does = "CAFS", however if there is something else in C6, it isn't running the alternate formula...but is rather just giving a "False" message. Based on this result, I am assuming that I am missing something that is causing the OR formula to get missed, but I can't track down where the issue is.

Any thoughts would be greatly appreciated!

Thanks,
Bill

Try Formulas -> Evaluate Formula to give you a clue.

Also rather than leave all the FALSE indicators in your formula blank, put some unique text in there to help find the IF where it is failing, ie. instead of ))) put ),"False1"),"False2")

Thanks for your reply Kersplash. Entering the "False1" and "False2" that you mentioned, just gives me the error "You've entered too many arguments for this function". The "Evaluate Formula" option runs through correctly if C6 is CAFS (which is the first variable to check), however if C6 is something other than CAFS, the evaluation just returns False...which I'm assuming means that the formula is simple stopping after getting the false response to the first validation, rather than executing the formula it should. This still raises the question of what I am doing wrong,with the if or statement....

Formulas -> Evaluate Formula may help you.

Hi Leelnich. Thank you so much for your response. It looks like the formula that you suggested is doing what I need it to do...although I'm not smart enough to know why it is... I'm not experienced with the Index command, so so I'm not exactly sure what it is doing...but like I said...it's doing what I need it to do