=IF(logical test,value if true,value if false) always backwards!

I think I'm losing my mind....

........C...................D...........................E.........
1......1............10/25/2005...........10/26/2005
2......2............10/27/2005...........10/29/2005
3......0............10/29/2005...........10/29/2005
4......3............10/29/2005...........11/01/2005

This is how the data SHOULD look.

Column C is the duration of the project in days. Column D is the project start date. Column E is the project end date.

In Column D I'm using the formula =IF(logical test,value if true,value if false). As example, in D4 I'm using the formula =IF(C3="0",E3+0,E3+1).

In my limited understanding, this is supposed to mean that if C3 is a 0, then D4 will report 10/29/2005, BUT if C3 is a number other than 0, then D4 will report 10/30/2005.

Here is the issue: the formula =IF(C3="0",E3+0,E3+1) is ignoring the E3+0 part - it ALWAYS adds 1 day to the duration,even when the C3=0. Also, I tried flipping the true-false values by using =IF(C3="0",E3+1,E3+0). In that case it ALWAYS adds 0 days to the duration, even when the value is something other than 0.

Any help would be GREATLY appreciated. I know it must be my misunderstanding how this formula is supposed to work.

Christine

Christine,

If your formula has the value zero in quotes

=IF(C3="0",....)

and C3 has numeric data, it will always be false and return the calculation for the false return. Try taking your qotation marks out of the formula so

=IF(C3=0,....)

The quotation marks make excel look for a text value not a numeric value.


Cheers,

Steve

Get rid of the quotes: =IF(C3=0,E3+0,E3+1) - remember to format the cell as
date otherwise a serial number will show. And since adding 0 does nothing,
you could use =IF(C3=0,E3,E3+1)
Or, get rid of the IF and use =E3+(C3>0)

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"chaminod" <chaminod.201gwy_1134578701.6511@excelforum-nospam.com> wrote in
message news:chaminod.201gwy_1134578701.6511@excelforum-nospam.com...
>
> I think I'm losing my mind....
>
> .......C...................D...........................E.........
> 1......1............10/25/2005...........10/26/2005
> 2......2............10/27/2005...........10/29/2005
> 3......0............10/29/2005...........10/29/2005
> 4......3............10/29/2005...........11/01/2005
>
> This is how the data SHOULD look.
>
> Column C is the duration of the project in days. Column D is the
> project start date. Column E is the project end date.
>
> In Column D I'm using the formula =IF(logical test,value if true,value
> if false). As example, in D4 I'm using the formula
> =IF(C3="0",E3+0,E3+1).
>
> In my limited understanding, this is supposed to mean that if C3 is a
> 0, then D4 will report 10/29/2005, BUT if C3 is a number other than 0,
> then D4 will report 10/30/2005.
>
> Here is the issue: the formula =IF(C3="0",E3+0,E3+1) is ignoring the
> E3+0 part - it ALWAYS adds 1 day to the duration,even when the C3=0.
> Also, I tried flipping the true-false values by using
> =IF(C3="0",E3+1,E3+0). In that case it ALWAYS adds 0 days to the
> duration, even when the value is something other than 0.
>
> Any help would be GREATLY appreciated. I know it must be my
> misunderstanding how this formula is supposed to work.
>
> Christine
>
>
> --
> chaminod
> ------------------------------------------------------------------------
> chaminod's Profile:
> http://www.excelforum.com/member.php...o&userid=18163
> View this thread: http://www.excelforum.com/showthread...hreadid=493465
>

Thanks so much for both of your speedy replies. Works great. I feel silly for such a basic question, but SO thrilled the Excel wizards out there are kind enough to set me straight!

Thanks again! This site has been most helpful over the last few months.

Try taking the quotes off the zero in your formula.........

=IF(C3=0,E3+0,E3+1)

Vaya con Dios,
Chuck, CABGx3


"chaminod" wrote:

>
> I think I'm losing my mind....
>
> ........C...................D...........................E.........
> 1......1............10/25/2005...........10/26/2005
> 2......2............10/27/2005...........10/29/2005
> 3......0............10/29/2005...........10/29/2005
> 4......3............10/29/2005...........11/01/2005
>
> This is how the data SHOULD look.
>
> Column C is the duration of the project in days. Column D is the
> project start date. Column E is the project end date.
>
> In Column D I'm using the formula =IF(logical test,value if true,value
> if false). As example, in D4 I'm using the formula
> =IF(C3="0",E3+0,E3+1).
>
> In my limited understanding, this is supposed to mean that if C3 is a
> 0, then D4 will report 10/29/2005, BUT if C3 is a number other than 0,
> then D4 will report 10/30/2005.
>
> Here is the issue: the formula =IF(C3="0",E3+0,E3+1) is ignoring the
> E3+0 part - it ALWAYS adds 1 day to the duration,even when the C3=0.
> Also, I tried flipping the true-false values by using
> =IF(C3="0",E3+1,E3+0). In that case it ALWAYS adds 0 days to the
> duration, even when the value is something other than 0.
>
> Any help would be GREATLY appreciated. I know it must be my
> misunderstanding how this formula is supposed to work.
>
> Christine
>
>
> --
> chaminod
> ------------------------------------------------------------------------
> chaminod's Profile: http://www.excelforum.com/member.php...o&userid=18163
> View this thread: http://www.excelforum.com/showthread...hreadid=493465
>
>