Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? -- Jimmydageek ------------------------------------------------------------------------ Jimmydageek's Profile: http://www.excelforum.com/member.php...o&userid=31692 View this thread: http://www.excelforum.com/showthread...hreadid=513933 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? -- Jimmydageek ------------------------------------------------------------------------ Jimmydageek's Profile: http://www.excelforum.com/member.php...o&userid=31692 View this thread: http://www.excelforum.com/showthread...hreadid=513933 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retry: VLOOKUP nested in IF Statement | Excel Worksheet Functions | |||
Problem with nested IF_OR statement | Excel Worksheet Functions | |||
Nested If statement | Excel Worksheet Functions | |||
7+ nested if statement? | Excel Worksheet Functions | |||
Combining SUM Function with Nested If Statement | Excel Discussion (Misc queries) |