Forum Discussion
Re: Welcome to the Excel Community
The following formula returns "You've entered too many arguments for this function". Where is the problem and how do I fix it? Thanks! =IF(BJ67>0,IF(BJ68>0,IF(BJ67<BJ68,BJ69,IF(BK67>0,IF(BK68>0,IF(BK67>BK68,BK69,IF(BL67>0,BL68<0,BL69,IF(BM67<0,BM68>0,BM69,IF(BN67<0,BN68<0,BN68<0,BN69,0)))))))
7 Replies
Nesting multiple IF statements makes it hard to read and very easy to misplace a comma or parenthesis. What are you trying to accomplish? Perhaps there is an easier way.
You cannot use multiple criteria arguments in IF. If you require multiple criteria to be satisfied, use AND to combine them. For example:
=IF(BJ67>0,IF(BJ68>0,IF(BJ67<BJ68,BJ69,IF(BK67>0,IF(BK68>0,IF(BK67>BK68,BK69,IF(AND(BL67>0,BL68<0),BL69,IF(AND(BM67<0,BM68>0),BM69,IF(AND(BN67<0,BN68<0,BN68<0),BN69,0)))))))))
But it's hard to understand the logic behind the formula, so I have no idea whether this does what you want.
Thank you VERY much! That was exactly what I needed. Your knowledge amazes me....
One last question. I have literally hundreds of formulas I have created, but they all have conditions where IF a cell is either greater than zero or less than zero. I have included a typical one below. What I failed to consider was what happened if the targeted cell actually WAS zero. How I can insert that condition (i.e. if targeted cell is NOT zero, then move on with the formula. I am hoping I can include this at the very start of the formulas, when I can hopefully do a find and replace with ALL the cells I just built. Perhaps it is an IF NOT situation, or perhaps I can do this with <> or ><. If I have to open a new parentheses ( with the additional argument, I need to know where to close it in the formula with a ). Your further help would be greatly appreciated! Thanks, Robin
=IF(AND(G73<0,G74>0),G73,0) What would you have the formula =IF(AND(G73<0,G74>0),G73,0) return if G73=0? And if G74=0?
