Solved

if / and statement - excel 2010

Posted on 2013-05-09
7
559 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:Frank .S
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

713 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