Re: Welcome to the Excel Community

Copper Contributor
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

@Rob_Cowlyn 

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.

@Rob_Cowlyn 

 

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.

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)

@Rob_Cowlyn 

What would you have the formula =IF(AND(G73<0,G74>0),G73,0) return if G73=0? And if G74=0?

I'm sorry but I don't understand. Can you show it with all commas and parentheses in place, please? The ? marks confuse me and this doesn't show me where to put the parentheses. My final zero in my EXAMPLE was the return if false (and I still need my false return, as it isn't always a zero but is often a cell reference). I just need to include, at the right place, (at the beginning if possible), an argument that supercedes my <0 or >0 throughout the whole formula if the reference cells are actually zeroes (not positive or negative numbers) then continue with rest of everything I wrote. So my formulas only come into play where the cells they are referring to are anything OTHER than an actual zero. Thank you!

@Rob_Cowlyn 

My previous reply was a question, hence the question marks.

You haven't told me what you want tie formula to return if one or both of the cells equals 0. If you want it to return a blank:

 

=IF(OR(G73=0, G74=0), "", IF(AND(G73<0, G74>0), G73, 0))

That really helped, Hans. Thank you so much for your help. I have it cracked now. Robin