IF Statement (Nested IF's??)

IF Statement (Nested IF's??)

Post by Jimmydagee » Sun, 19 Feb 2006 13:20:59



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.yqcomputer.com/
View this thread: http://www.yqcomputer.com/
 
 
 

IF Statement (Nested IF's??)

Post by JJ » Sun, 19 Feb 2006 13:48:13


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

 
 
 

IF Statement (Nested IF's??)

Post by Jimmydagee » Sun, 19 Feb 2006 14:06:02


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.yqcomputer.com/
View this thread: http://www.yqcomputer.com/
 
 
 

IF Statement (Nested IF's??)

Post by Biff » Sun, 19 Feb 2006 14:37:29

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" < XXXX@XXXXX.COM >
 
 
 

IF Statement (Nested IF's??)

Post by Bob Philli » Sun, 19 Feb 2006 20:34:54

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" < XXXX@XXXXX.COM >


http://www.yqcomputer.com/
 
 
 

IF Statement (Nested IF's??)

Post by Jimmydagee » Mon, 20 Feb 2006 08:38:10


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.yqcomputer.com/
View this thread: http://www.yqcomputer.com/
 
 
 

IF Statement (Nested IF's??)

Post by Pete_U » Mon, 20 Feb 2006 10:06:37

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??)

Post by daddylongl » Mon, 20 Feb 2006 10:21:47


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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.yqcomputer.com/
View this thread: http://www.yqcomputer.com/
 
 
 

IF Statement (Nested IF's??)

Post by Bob Philli » Mon, 20 Feb 2006 10:37:20

How about

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jimmydageek" < XXXX@XXXXX.COM >


http://www.yqcomputer.com/