IF Statement (Nested IF's??)

[Jimmydageek]

Ok so I am pretty good with Excel and I know about IF Statements in a Novice fashion but this one has got me.

This is what I want to do. There are two Cell's that I am referenceing in a seperate Cell. To add some kind of name to it I am trying to Reference cell D40 & D56. I basically want these cells to do this =D40-D56 if there are "Values" in both of those Cells. If there is "n/a" (actually typed into) in either of those cells then I want a "ND" to appear in the 3rd Cell. This is what I am stumped by.

I have this so far...maybe need more...maybe I am going the wrong direction with this. I figured it must be a nesting of some kind but hey....I'm lost. Please Help! TIA! Jimmy

=IF(D40="n/a","ND",IF(D56="n/a","ND",IF(

[JJ]

Jimmydageek wrote:
> Ok so I am pretty good with Excel and I know about IF Statements in a
> Novice fashion but this one has got me.
>
> This is what I want to do. There are two Cell's that I am referenceing
> in a seperate Cell. To add some kind of name to it I am trying to
> Reference cell D40 & D56. I basically want these cells to do this
> =D40-D56 if there are "Values" in both of those Cells. If there is
> "n/a" (actually typed into) in either of those cells then I want a "ND"
> to appear in the 3rd Cell. This is what I am stumped by.
>
> I have this so far...maybe need more...maybe I am going the wrong
> direction with this. I figured it must be a nesting of some kind but
> hey....I'm lost. Please Help! TIA! Jimmy
>
> =IF(D40="n/a","ND",IF(D56="n/a","ND",IF(
>
>
IF(AND(D40="n/a",D56="n/a"),"ND",D40-D56)

[Jimmydageek]

So funny. I figured it out just and was getting ready to post it and saw your Post JJ. Mine was different though but the outcome the same.

=IF(D40="n/a","ND",IF(D56="n/a","ND",D40-D56))

I will try yours also though....Nope..Didn't work...Got a #Value!.

Thanks for helping out I really appreciate it!

[Biff]

Hi!

What are all the possible types of entries in these cells?

Can 1 cell have a number and the other be empty? Can they both be empty? Can
one cell have a number and the other have "n/a"?

As a starting point:

=IF(COUNT(D40,D56)=2,D40-D56)

If you don't have to consider empty cells and the only other possible
entries could be "n/a":

=IF(COUNT(D40,D56)=2,D40-D56,"ND")

Biff

"Jimmydageek" <[email protected]>
wrote in message
news:[email protected]...
>
> Ok so I am pretty good with Excel and I know about IF Statements in a
> Novice fashion but this one has got me.
>
> This is what I want to do. There are two Cell's that I am referenceing
> in a seperate Cell. To add some kind of name to it I am trying to
> Reference cell D40 & D56. I basically want these cells to do this
> =D40-D56 if there are "Values" in both of those Cells. If there is
> "n/a" (actually typed into) in either of those cells then I want a "ND"
> to appear in the 3rd Cell. This is what I am stumped by.
>
> I have this so far...maybe need more...maybe I am going the wrong
> direction with this. I figured it must be a nesting of some kind but
> hey....I'm lost. Please Help! TIA! Jimmy
>
> =IF(D40="n/a","ND",IF(D56="n/a","ND",IF(
>
>
> --
> Jimmydageek
> ------------------------------------------------------------------------
> Jimmydageek's Profile:
> http://www.excelforum.com/member.php...o&userid=31692
> View this thread: http://www.excelforum.com/showthread...hreadid=513933
>

[Bob Phillips]

That does work for me, but perhaps you really want

=IF(OR(D40="n/a",D56="n/a"),"ND",D40-D56))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jimmydageek" <[email protected]>
wrote in message
news:[email protected]...
>
> So funny. I figured it out just and was getting ready to post it and
> saw your Post JJ. Mine was different though but the outcome the same.
>
> =IF(D40="n/a","ND",IF(D56="n/a","ND",D40-D56))
>
> I will try yours also though....Nope..Didn't work...Got a #Value!.
>
> Thanks for helping out I really appreciate it!
>
>
> --
> Jimmydageek
> ------------------------------------------------------------------------
> Jimmydageek's Profile:
http://www.excelforum.com/member.php...o&userid=31692
> View this thread: http://www.excelforum.com/showthread...hreadid=513933
>

[Jimmydageek]

I am not too worried about it as it is working now but yes there would always either be a number or an N/A in there.

I posted another question similar to this but not getting much response too. Here is my problem.

I have a report I put out and need to get the difference between the two cells. The report populates the times in the following formation :20:30. It does not put anything in the hour field when you pull the report if it does not go past an hour of time.

I get an error trying to get the day before stats difference from the present days stats. No formatting works. And there are way too many fields to go in and manually enter a 0 for each cell that does not contain an hour.

Thoughts?

[Pete_UK]

Do you mean you want it to show 0:20:30 ? If so, just format the cell
(highlight all the cells first if there are many of them) using Format
| Cell | Number (tab) and select Time and choose 37:30:55 from the list
- this is effectively the same as [h]:mm:ss.

Hope this helps.

Pete

[daddylonglegs]

If you can't reformat your :20:30 it may be text, you could perhaps convert in another column, e.g. in B1

=(0&A1)+0

format as h:mm:ss and copy down column. If you want to use these values to replace your originals, copy column then use Edit > Paste Special >Values to replace

[Bob Phillips]

How about

=D40-IF(LEFT(D56,1)=":","0"&D56,D56)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jimmydageek" <[email protected]>
wrote in message
news:[email protected]...
>
> I am not too worried about it as it is working now but yes there would
> always either be a number or an N/A in there.
>
> I posted another question similar to this but not getting much response
> too. Here is my problem.
>
> I have a report I put out and need to get the difference between the
> two cells. The report populates the times in the following formation
> :20:30. It does not put anything in the hour field when you pull the
> report if it does not go past an hour of time.
>
> I get an error trying to get the day before stats difference from the
> present days stats. No formatting works. And there are way too many
> fields to go in and manually enter a 0 for each cell that does not
> contain an hour.
>
> Thoughts?
>
>
> --
> Jimmydageek
> ------------------------------------------------------------------------
> Jimmydageek's Profile:
http://www.excelforum.com/member.php...o&userid=31692
> View this thread: http://www.excelforum.com/showthread...hreadid=513933
>