IF Statement (Nested IF's??)

J

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(
 
J

JJ

Jimmydageek said:
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)
 
J

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! :)
 
B

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]...
 
J

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

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top