Solved

if / and statement - excel 2010

Posted on 2013-05-09
7
538 Views
Last Modified: 2013-05-21
hi experts im trying to come up with a formula to do the following'

=IF(&(C12="",c9="","",C12/$C$9+0.001)

so what im trying to do here is;
if c12 and/ or c9 = "","" otherwise c12/$c$9+0.001)

please assist with this formula
0
Comment
Question by:FrankSasso
7 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 275 total points
ID: 39154423
=IF(OR(C9="",C12=""),"",C12/$C$9+0.001)
0
 
LVL 81

Expert Comment

by:byundt
ID: 39154425
At the risk of being a trifle confusing, you can achieve the same result with:
=IF(C9&C12="","",C12/$C$9+0.001)
0
 
LVL 15

Expert Comment

by:Jagadishwor Dulal
ID: 39154506
In your Support :
=IF(AND(C9<>"",C12<>""),(($C$12/$C$9)+0.001),"")

Open in new window

Please Follow Syntax
=if(and(condition1, condition2), TrueResult, FalseResult)

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:JHalliday
ID: 39154702
The original poster is after an AND/OR statement @byundt is correct as this will trap both conditions and is also using the correct functionality in his first post but shows a compatible syntax in his second post for older version of Excel.

@jagadishdulal only shows the syntax for if both cells were empty.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39154837
Unless I am still asleep, this
=IF(C9&C12="","",C12/$C$9+0.001)
appears to be the same as
=IF(AND(C9="",C12=""),"",C12/$C$9+0.001)
rather than
=IF(OR(C9="",C12=""),"",C12/$C$9+0.001)
0
 
LVL 5

Expert Comment

by:JHalliday
ID: 39154844
@rorya lol I think its me that's half asleep yes you are correct :)
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 275 total points
ID: 39155078
With the benefit of some sleep, I now see that my two formulas may return different results--my goof! The first one is an OR and the second an AND with respect to cells C9 and C12 being blank.

If the goal is to avoid DIV/0! error value, then you need:
=IF(N($C$9)=0,"",C12/$C$9+0.001)
This formula traps C9 being either 0 or blank

If the goal is also to avoid 0 as a meaningless result, then you need:
=IF(OR(N($C$9)=0,N(C12)=0),C12/$C$9+0.001)
This formula traps both C9 and C12 as being either 0 or blank
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now