IF AND formulas in Excel

Microsoft Microsoft excel 2007 full vers...
December 9, 2010 at 11:17:56
Specs: Windows XP
I am relatively new to "complex" Excel forumals and am having a heck of a time figuring out what is going wrong. It could be anything from a silly parenthesis or comma to my argument is just completely incorrect. Any advice you could give would be GREAT.

=IF((H2>I2),0,(IF((K2=H2),-1,(IF(AND(H2<=I2,K2>=J2),NETWORKDAYS((I2),(H2),(IF(AND(H2<=I2,K2<=I2),NETWORKDAYS((K2),(H2))))))))))

I keep getting a #VALUE! error.


See More: IF AND formulas in Excel

Report •


#1
December 9, 2010 at 13:02:01
Instead of making us trying to figure out what you are trying to do, please give us some numbers to put in each cell and the expected output from those numbers.

The first thing I can tell you is that it appears that you have way too many parenthesis. They may not be the problem, but they certainly make the formula harder to read.

e.g.

=IF((H2>I2),0,(IF((K2=H2),-1...

could be written as:

=IF(H2>I2,0,IF(K2=H2,-1...

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 9, 2010 at 15:36:22
Your braces are in the wrong place. This is how it should look:
=IF((H2>I2),0,(IF((K2=H2),-1,(IF(AND(H2<=I2,K2>=J2),NETWORKDAYS((I2),(H2)),(IF(AND(H2<=I2,K2<=I2),NETWORKDAYS((K2),(H2)))))))))

Your first NETWORKDAYS has a brace missing and you have one brace too many at the end.

You may be interested in looking here:
http://www.ozgrid.com/News/ExcelLog...

___________________________________________
When everything else fails, read the instructions.


Report •

#3
December 9, 2010 at 16:14:39
re: "Your first NETWORKDAYS has a brace missing..."

I guess it depends on how you look at it. mosaddique feels there is a brace (parenthesis) missing, and I feel that there are 3 too many.

The Help file for NETWORKDAYS show this syntax:

NETWORKDAYS(start_date,end_date,holidays)

There are no parenthesis around the start_date or end_date.

mosaddique feels it should look like this:

NETWORKDAYS((I2),(H2))

I feel it should look like this:

NETWORKDAYS(I2,H2)

The same holds for your IF functions. You don't need parenthesis around the arguments/cell references within the IF function.


As I said in Response #1, you have way too many unnecessary parenthesis which makes your formula very hard to read.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 9, 2010 at 17:13:43
I was unsure whether braces were required around cell references so I erred on the side of caution.

However DerbyDad03 is absolutely correct. You have too many braces.

___________________________________________
When everything else fails, read the instructions.


Report •

#5
December 10, 2010 at 07:38:42
Thank you for your replies. The parenthesis don't appear to be the issue. Here is what I'm trying to do....

H2-K2 are dates.

If H2 is a date greater than I2 then I need the result to be 0. (This part of the formula appears to be working.)

If K2 = H2 then I need the result to be -1. (This also appears to be working.)

If H2 is a date less than or equal to I2 AND K2 is less than or equal to I2 then I need the result to be the networkdays between K2 and H2, otherwise, I need the result to be the network days between I2 and H2. (The first portion appears to work on lines with cells that fit the criteria, however the false result is not coming up when the cells do not fit the criteria.)

I hope this helps. It seems like it'd be easy enough but I just can't get it to work.


Report •

#6
December 10, 2010 at 07:55:34
I am unsure what you are after because of your statement
however the false result is not coming up when the cells do not fit the criteria:

You do not have an else to that last condition.
So all you will get is FALSE as a result.
You could try putting in an explicit else which is set to give FALSE.

OR you need to add the following else case
,NETWORKDAYS(I2,H2)
if you do not want the FALSE result

___________________________________________
When everything else fails, read the instructions.


Report •


Ask Question