IF Statement (Nested IF's??)
 

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

IF Statement (Nested IF's??)
 

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)

IF Statement (Nested IF's??)
 

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

IF Statement (Nested IF's??)
 

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"
wrote in message
...

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

IF Statement (Nested IF's??)
 

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"
wrote in message
...

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

IF Statement (Nested IF's??)
 

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?:confused:


--
Jimmydageek
------------------------------------------------------------------------
Jimmydageek's Profile: http://www.excelforum.com/member.php...o&userid=31692
View this thread: http://www.excelforum.com/showthread...hreadid=513933

IF Statement (Nested IF's??)
 

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

IF Statement (Nested IF's??)
 

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513933

IF Statement (Nested IF's??)
 

How about

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jimmydageek"
wrote in message
...

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?:confused:


--
Jimmydageek
------------------------------------------------------------------------
Jimmydageek's Profile:
http://www.excelforum.com/member.php...o&userid=31692
View this thread: http://www.excelforum.com/showthread...hreadid=513933