IF function
Hi,
I have this formula in Excel, but it only works in some cases, not all. I can't figure out why, maybe someone can help me with this. IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2))) Thanx! |
When do you get errors?
"JN" wrote: Hi, I have this formula in Excel, but it only works in some cases, not all. I can't figure out why, maybe someone can help me with this. IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2))) Thanx! |
The formula doesn't work for years 19XX -2003. Based on the formula, it
should be 0. However, they are showing up with negative numbers. JN "bj" wrote: When do you get errors? "JN" wrote: Hi, I have this formula in Excel, but it only works in some cases, not all. I can't figure out why, maybe someone can help me with this. IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2))) Thanx! |
I don't know what is happening,
but you could try =if(AE2=datevalue("7/29/05"),0,max(0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2))) instead "JN" wrote: The formula doesn't work for years 19XX -2003. Based on the formula, it should be 0. However, they are showing up with negative numbers. JN "bj" wrote: When do you get errors? "JN" wrote: Hi, I have this formula in Excel, but it only works in some cases, not all. I can't figure out why, maybe someone can help me with this. IF(ISERROR(IF(OR(AG2<0,AE2<DATEVALUE("01/01/05"),AE2<="07/01/05"),0,AG2*NETWORKDAYS($B$265,MIN(AE2,DATEVALUE("1 2/31/05")))/NETWORKDAYS("07/29/05",AE2))),0,IF(OR(AG2<0,AE2<DATEVALUE("1/1/05"),AE2<="07/29/05"),0,AG2*NETWORKDAYS("07/29/05",MIN(AE2,DATEVALUE("12/31/05")))/NETWORKDAYS("07/29/05",AE2))) Thanx! |
All times are GMT +1. The time now is 09:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com